Math
ROUND function in Excel:syntax and examples
ROUND leaves a number with the number of decimals you choose. In Spanish Excel it is called REDONDEAR. Syntax: =ROUND(number, num_digits).
Syntax
=ROUND(number, num_digits)In English Excel, arguments are separated with a comma (,). In Spain and much of Europe and Latin America, Excel uses a semicolon (;).
Arguments
| Argument | What it is |
|---|---|
number | Required. The number, cell or calculation you want to round. Example: B2*1.0825. |
num_digits | Required. How many decimals you keep. 2 for cents, 0 for a whole number, and negatives for tens (-1) or hundreds (-2). Example: 2. |
How do you use ROUND? A step-by-step example
ROUND leaves a number with the decimals you need. In this example you add 8.25% sales tax to some prices and the result comes out with 4 or 6 decimals.
| A | B | C | |
|---|---|---|---|
| 1 | Product | Before tax | With tax (=B*1.0825) |
| 2 | Stapler | 4.95 | 5.358375 |
| 3 | A5 notebook | 2.30 | 2.48975 |
| 4 | Markers | 6.80 | 7.361 |
=ROUND(B2*1.0825,2) returns 5.36.
B2*1.0825is the calculation you want to round. It can go inside ROUND, with no helper column.2is the decimals: cents.- The third decimal decides: 5.358375 has a 8 after 5.35, so it goes up to 5.36. With the notebook,
=ROUND(B3*1.0825,2)goes from 2.48975 to 2.49.
Sales tax is 8.25% in this example. Swap the 1.0825 for your own rate.
What do you put in num_digits?
| num_digits | Rounds to | Example | Result |
|---|---|---|---|
| 2 | Cents | =ROUND(B2*1.0825,2) | 5.36 |
| 1 | Tenths | =ROUND(1234.5678,1) | 1234.6 |
| 0 | Whole number | =ROUND(1234.5678,0) | 1235 |
| -2 | Hundreds | =ROUND(1234.5678,-2) | 1200 |
When the deciding digit is a 5, ROUND goes away from zero: =ROUND(2.5,0) returns 3 and =ROUND(-2.5,0) returns -3.
ROUND, ROUNDUP, ROUNDDOWN or the format?
| What it does | Example | Result | |
|---|---|---|---|
| ROUND | To the nearest | =ROUND(53/12,0) | 4 |
| ROUNDUP | Always up | =ROUNDUP(53/12,0) | 5 |
| ROUNDDOWN | Always down | =ROUNDDOWN(2.789,2) | 2.78 |
| INT | To the lower integer | =INT(-2.5) | -3 |
| TRUNC | Removes the decimals | =TRUNC(-2.5) | -2 |
| Cell format | Changes what you see, not the value | Decrease Decimal button | The value keeps all its decimals |
Which one to pick:
- Prices, scores or invoice amounts: ROUND.
- Boxes, buses, seats: ROUNDUP. With 53 units in boxes of 12 you need 5 boxes, not 4.
- Only to make it look cleaner, without using the number in other calculations: the format.
Why does the total not match by a cent?
Because the format hides decimals. If three cells hold 10/3 (3.3333…) and look like 3.33, =SUM(E2:E4) returns 10, not 9.99. Both figures are correct: 10 is the sum of the real values, and 9.99 of the ones you see.
On an invoice, round each line before you add. That way the total is the sum of what is printed: =ROUND(E2,2)*3 gives 9.99. The same happens with the prices with tax above: =SUM(C2:C4) gives 15.209125, and =ROUND(SUM(C2:C4),2) gives 15.21.
How does ROUND show up in an Excel test?
In a job interview it usually sits inside another calculation: "Work out the price with tax, rounded to cents" or "the average score with one decimal." What they look at: that you round the final result with ROUND, not only with the format.
In a skills test:
What does
=ROUND(7.456,1)return? a) 7.4 · b) 7.45 · c) 7.5 · d) 7
Answer: c. With 1 decimal it looks at the second, which is a 5, and rounds up.
Practice ROUND
The English exercises are on the way. Until then, copy the prices into a blank sheet, add the tax, and round each one. Then try it with AVERAGE to round a mean to one decimal, and add the lines with SUM to see the cent. To round only when a condition holds, wrap it in IF.
Available in: all versions of Excel. Source: Microsoft: ROUND function. Results calculated with Formulo's engine.
Common mistakes
- The total is off by a cent
- The cells show 2 decimals but store more. With 3.3333… in three cells, the sum gives 10, even though the three cells look like 3.33. If the total has to be the sum of what you see, round each line: =ROUND(E2,2)*3 gives 9.99.
- Excel does not accept the formula
- num_digits is missing. Both arguments are required, so ROUND with only a number cannot be entered. Use 0 if you want a whole number.
- You wanted to round up and it went down
- ROUND goes to the nearest: =ROUND(53/12,0) returns 4, because 53/12 is 4.4166… For boxes, seats or people, use ROUNDUP: =ROUNDUP(53/12,0) returns 5.
- You changed the format and the number stays the same
- The Decrease Decimal button changes what you see, not the value. If that number feeds other calculations, apply ROUND.
Frequently asked questions
What is ROUND called in Spanish?
REDONDEAR. In a Spanish Excel: =REDONDEAR(B2*1,0825;2), with a decimal comma and a semicolon as the separator.
How do I round to 2 decimals?
With 2 as the second argument: =ROUND(B2*1.0825,2). A 4.95 price plus 8.25% tax goes from 5.358375 to 5.36.
How do I always round up?
With ROUNDUP: =ROUNDUP(2.781,2) returns 2.79. To always round down, ROUNDDOWN: =ROUNDDOWN(2.789,2) returns 2.78.
How do I round to hundreds or thousands?
With negative digits: =ROUND(1234.5678,-2) returns 1200. With -3 you round to thousands.
What is the difference between INT and TRUNC?
With positives, none. With negatives, INT goes down to the lower integer and TRUNC only removes the decimals: =INT(-2.5) returns -3 and =TRUNC(-2.5) returns -2.
Does a 5 round up?
Yes, away from zero: =ROUND(2.5,0) returns 3 and =ROUND(-2.5,0) returns -3.
Related functions
Practice in a real spreadsheet
Exercises with real data and instant checking. The first three are free.