SheetSense

ROUND, ROUNDUP and ROUNDDOWN

ROUND rounds a number to a chosen number of decimal places. ROUNDUP always rounds away from zero and ROUNDDOWN toward zero.

fx
=ROUND(A2,2)

The sheet: Column A holds prices after a currency conversion, such as 12.3456. You want them rounded to cents.

How it works, step by step

  1. 01A2 is the number to round.
  2. 022 is the number of decimal places to keep. Use 0 for whole numbers.
  3. 03A negative value rounds to the left of the decimal, so -2 rounds to the nearest hundred.
  4. 04ROUNDUP(A2,2) and ROUNDDOWN(A2,2) use the same arguments with a fixed direction.

The same logic in four tools

Excel
=ROUND(A2,2)
Google Sheets
=ROUND(A2,2)
SQL
SELECT ROUND(price::numeric, 2) AS price_rounded
FROM prices;  -- PostgreSQL
Python (pandas)
import numpy as np

# Excel rounds halves away from zero. Series.round() rounds halves to even.
df["price_rounded"] = np.sign(df["price"]) * np.floor(np.abs(df["price"]) * 100 + 0.5) / 100

Mistakes that break it

  • !Cell formatting only changes what you see. ROUND changes the stored value, which matters when you add the column up.
  • !pandas Series.round(2) rounds halves to the nearest even digit, so 0.125 and 0.135 can differ from Excel.
  • !Binary floating point means values such as 2.675 may not round the way you expect in any tool.
  • !ROUNDUP rounds away from zero, so -1.21 becomes -1.3 with one decimal.

Common questions

How do I round to the nearest 5?
Use MROUND(A2,5).
What is the difference from INT?
INT rounds down to the next lower whole number, so INT(-1.5) is -2. ROUNDDOWN(-1.5,0) is -1.

Got a version of this with your own ranges?

Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.