Summary

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:

=CEILING(B5,0.5)

Generic formula

=CEILING(number,0.5)

Explanation 

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 Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.