Round a price to end in .99

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.