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 .
Explanation
In the example shown, the goal is to round a price to the nearest value ending in .99. So, for example, if a price is currently $5.31, the result should be $4.99. The best way to think about the problem is to restate it as “round a price to the nearest whole dollar, less 1 penny”. In other words, the solution works in two parts: (1) round and (2) subtract.
For rounding, we use the ROUND function, with the num_digits argument set to zero (0) for no decimal places:
=ROUND(B6,0) // nearest dollar
The ROUND function with a zero will round to the nearest whole dollar. Once rounded, the formula simply subtracts 0.01 to get a .99 value. The formula in C6, copied down, is:
=ROUND(B6,0)-0.01
With the value in B6 of 63.39, the formula is solved like this:
=ROUND(B6,0)-0.01
=ROUND(63.39,0)-0.01
=63-0.01
=62.99
With MROUND
Other option for rounding in this case is the MROUND function . Instead of rounding to a specific number of decimal places, the MROUND rounds to the nearest multiple, provided as the significance argument. This means we can use MROUND to round to the nearest dollar by providing a multiple of 1 like this:
=MROUND(B6,1) // nearest dollar
The equivalent formula is then:
=MROUND(B6,1)-0.01
To force rounding up or down to the nearest multiple, see the CEILING and FLOOR functions.
