When working with prices, it can often be beneficial to round to the nearest .99 cents. There are a few formulas we can use depending on the results we're looking for.
Contents:
Formulas
Assuming your price in in cell A1:
Round Price to Nearest .99
= ROUND(A1,0) - 0.01Round Price Up to Nearest .99
= CEILING.MATH(A1,1) - 0.01Round Price Down to Nearest .99
= FLOOR.MATH(A1,1) - 0.01How to Round Price to End in Nearest .99
If we have a list of prices and want to round them up or down to the nearest .99 cents, we can use the following formula:
= ROUND(price,0) - 0.01The ROUND function rounds our price to the nearest whole number ($5.30 to $5.00), and then we subtract 0.01 from that number to reach the nearest .99 cents.
How to Round Price Up to End in .99
Instead, if we have a list of prices and want to round them up to the nearest .99 cents, we can use the following formula:
= CEILING.MATH(price,1) - 0.01The CEILING.MATH function rounds our price up to the nearest whole number ($5.30 to $6.00), and then we subtract 0.01 from that number to reach the nearest .99 cents.
How to Round Price Down to End in .99
Otherwise, if we have a list of prices and want to round them down to the nearest .99 cents, we can use the following formula:
= FLOOR.MATH(price,1) - 0.01The FLOOR.MATH function rounds our price to the nearest whole number ($5.30 to $5.00), and ten we subtract 0.01 from that number to reach the nearest .99 cents.