Quick, clean, and to the point

Round a number up to next half

Excel formula: Round a number up to next half
Generic formula 

To round a number up to the next half, you can use the CEILING function, which always rounds up based on a given multiple. In the example shown, the formula in C5 is:


The Excel CEILING function rounds a number up to a given multiple. The multiple to use for rounding is given as the second argument (significance). If the number is already an exact multiple, no rounding occurs. 

In this example, we want to round up to the nearest half, so we provide 0.5 to CEILING as the multiple. In the example shown, the formula in C5 is:

=CEILING(B5,0.5) // returns 1.5

Even though the value in B5 is 1.1, CEILING rounds up to the next multiple of .5, which is 1.5.

CEILING works like the MROUND function, but unlike MROUND, which rounds to the nearest multiple, CEILING always rounds up to the given multiple. If you want to round down to the nearest half, you can use the FLOOR function.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.