Exceljet

Quick, clean, and to the point

Round a price to end in .99

Excel formula: Round a price to end in .99
Generic formula 
=ROUND(price,0)-0.01
Summary 

To round prices to end in the nearest, .99 value, you can use the ROUND function then subtract .01. In the example shown, the formula in C6 is:

=ROUND(B6,0)-0.01

which rounds the value in B6 to the nearest whole dollar, then subtracts .01.

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.