which rounds prices as shown in the screenshot.
Rounding rules
In the example shown, the goal is to round prices to end in either .45 or .95, following these rules: To round up to the nearest half dollar, we use the CEILING function, with the significance argument set to .5: This will round the original price up to the next half dollar. For example, $4.31 will become $4.50, and $5.72 will become $6.00. Importantly, if a price already ends in .00 or .50, it will remain unchanged (i.e. a price of $4.00 or $4.50 is not affected). Once rounded, the formula simply subtracts 0.05 to get a .45 or .95 result. The formula in C5, copied down, is: When B5 contains $17.01, the formula is solved like this:
About CEILING
CEILING is one of 8 rounding functions in Excel. You can use CEILING to do things like:
Round numbers up to multiples of 25 Round time up to 15 minute multiples Round materials up to the next whole unit
The MROUND function and FLOOR function can also round to a given multiple, but the behavior is different from CEILING:
CEILING rounds up to the next multiple FLOOR rounds down to the previous multiple MROUND rounds to the nearest multiple
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.
