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.
=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
- 01A2 is the number to round.
- 022 is the number of decimal places to keep. Use 0 for whole numbers.
- 03A negative value rounds to the left of the decimal, so -2 rounds to the nearest hundred.
- 04ROUNDUP(A2,2) and ROUNDDOWN(A2,2) use the same arguments with a fixed direction.
The same logic in four tools
=ROUND(A2,2)=ROUND(A2,2)SELECT ROUND(price::numeric, 2) AS price_rounded
FROM prices; -- PostgreSQLimport 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) / 100Mistakes 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.