which rounds the value in B6 to the nearest whole dollar, then subtracts .01. For rounding, we use the ROUND function, with the num_digits argument set to zero (0) for no decimal places: 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: With the value in B6 of 63.39, the formula is solved like this:
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: The equivalent formula is then: To force rounding up or down to the nearest multiple, see the CEILING and FLOOR functions.
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.
