Round Price to Nearest Dollar or Cent in Excel & Google Sheets
Written by
Reviewed by
Download the example workbook
This tutorial will demonstrate how to round a price to the nearest dollar or cent in Excel and Google Sheets.
Round Price to Nearest Dollar
There are several rounding functions that you can use to round prices. We will walk through three functions below and a few ways to use them. They all call for the same two arguments: number (your original decimal value) and num_digits (the number of digits you want after the decimal place). Input 0 for num_digits to round to the nearest dollar.
In this Article
ROUND Function
The ROUND Function can round a currency value to the nearest dollar using standard rounding rules (anything below $0.50 rounds down, otherwise rounds up).
=ROUND(B3,0)
ROUNDUP Function
The ROUNDUP Function will round up to the nearest dollar.
=ROUNDUP(B3,0)
ROUNDDOWN Function
The ROUNDDOWN Function will round down to the nearest dollar.
=ROUNDDOWN(B3,0)
Round Price to Nearest Cent
By setting the num_digits input to 2, we can round to the nearest cent.
ROUND Function
=ROUND(B3,2)
ROUNDUP Function
=ROUNDUP(B3,2)
ROUNDDOWN Function
=ROUNDDOWN(B3,2)
Round Price to End in .99
“Charm pricing” is a sales tactic where the price is set just below a round number by using 99¢. It seems lower than the next whole number without having to sacrifice much profit.
By subtracting .01 from any of the above formulas, we can round a price to end in .99.
=ROUND(B3,0)-0.01
Round Price to Nearest Dollar or Cent in Google Sheets
All of the above examples work exactly the same in Google Sheets as in Excel.
Tips
- You can change number formatting if you want to keep the original decimal value but display as a round number.
- Use Paste Special if you want to replace the original values with the rounded prices and save them as values.
