Maths and conditional totals

Excel ROUND Function: Round Numbers to a Set Number of Places

Rounds a number to the number of decimal places you specify.

ROUND takes a number and a number of decimal places, and rounds to it — half and above goes up, below half goes down. =ROUND(89.994, 2) gives 89.99 and =ROUND(89.995, 2) gives 90.00.

The second argument does more than decimals. Zero rounds to a whole number, and negative values round to the left of the decimal point: -1 to the nearest ten, -2 to the nearest hundred, -3 to the nearest thousand. That last one is how you turn a column of exact figures into a readable summary in thousands.

The important distinction is between rounding and formatting. Setting a cell to show two decimals changes what you see and nothing else — the stored value keeps every digit, and a column of such cells will produce a total that does not match the numbers displayed above it. ROUND changes the value itself, which is why financial models round explicitly rather than relying on formats.

Syntax

=ROUND(number, num_digits)

Arguments

number
Required
The value to round.
num_digits
Required
How many decimal places. 2 for pennies, 0 for whole numbers, -3 for the nearest thousand. Both arguments are required.

The example data

Headers in row 1, data in A2:D6. Note the blank in C4 and the text in C6.

ABCD
1ItemUnitsUnit priceRegion
2Cordless drill1289.99North
3Extension lead4012.5South
4Safety goggles8North
5Work gloves255.4South
6Tool belt6n/aNorth

Worked examples

=ROUND(C2, 1)

Result: 90

89.99 to one decimal place. The trailing zero is hidden by formatting.

=ROUND(B2 * C2, 2)

Result: 1079.88

The pattern that matters: round the result of a calculation to pennies, so the total of the column agrees with the rows.

=ROUND(B2 * C2, -2)

Result: 1100

A negative digits argument rounds to the left of the point — here, the nearest hundred.

=ROUNDUP(C4 + 0.001, 0)

Result: 1

ROUNDUP always goes away from zero regardless of the digit, which is how you price by whole units.

Now practise it

Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.

Open the exercise: ROUND Function

Common errors and how to fix them

#VALUE!

Why it happens: The number argument is text, or num_digits was left out.

How to fix it: Both arguments are required — =ROUND(A2) is invalid where many other languages would allow it.

Column total is a penny out

Why it happens: The cells are formatted to two decimals but hold more, so the displayed figures do not add up to the displayed total.

How to fix it: Round the values themselves with ROUND rather than formatting them, wherever the numbers will be read and checked.

Rounding goes the wrong way on .5

Why it happens: ROUND rounds half away from zero, so -2.5 becomes -3. Some standards expect half-to-even instead.

How to fix it: Excel has no banker's rounding function; build it with MROUND or accept the difference and document it.

Tips worth knowing

  • ROUNDUP and ROUNDDOWN ignore the halfway rule and always go one way — use them for pricing and capacity, not for reporting.
  • MROUND rounds to a multiple: =MROUND(A2, 0.05) snaps a price to the nearest five pence.
  • INT truncates towards negative infinity while TRUNC just cuts digits off; they differ on negative numbers.
  • Round once, at the end of a calculation chain. Rounding at every step compounds the error you were trying to remove.

Frequently asked questions

What is the difference between ROUND and cell formatting?

Formatting changes only what is displayed; the cell still holds the full-precision value and any calculation uses it. ROUND changes the stored value. This is why a formatted column can visibly fail to add up to its own total, and why financial work rounds explicitly.

How do I round to the nearest thousand?

Pass -3 as the second argument: =ROUND(A2, -3). Negative digit counts round to the left of the decimal point, so -1 is the nearest ten, -2 the nearest hundred, and so on.

What is the difference between ROUND, ROUNDUP and ROUNDDOWN?

ROUND uses the usual halfway rule. ROUNDUP always moves away from zero and ROUNDDOWN always moves toward it, whatever the digit — so ROUNDUP(2.01, 0) is 3 and ROUNDDOWN(2.99, 0) is 2.

Related functions

Guides that use it

Longer reads where this function does real work in a real sheet.

All functions