Excel practice › Excel functions › ROUND
ROUND in Excel: round the value, not only how it looks
ROUND rounds a number to the number of digits you ask for. With 692.7125 in D2, =ROUND(D2,2) gives 692.71.
The result is a new value, not a new look. Formatting a cell to two decimals changes what you see; ROUND changes the number that every later formula works with.
Syntax
=ROUND(number, num_digits)
number- The number to round: a value, a cell, or a whole calculation such as
B2*C2. num_digits- How many digits to round to. Above 0: that many decimal places. 0: the nearest whole number. Below 0: to the left of the decimal point, so
-1is the nearest 10 and-2the nearest 100. Required.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee | Hours | Rate | Pay | Rounded | |
| 2 | Avery | 37.75 | 18.35 | 692.71 | 692.71 | |
| 3 | Jordan | 40 | 21.4 | 856 | ||
| 4 | Casey | 28.25 | 17.85 | 504.26 | ||
| 5 | Riley | 33.25 | 19.85 | 660.01 | ||
| 6 | Morgan | 38.25 | 20.65 | 789.86 |
2 and watch: 1 gives 692.7, 0 gives 693, -1 gives 690 and -2 gives 700.What num_digits does
The same number, 692.7125, with each kind of num_digits:
=ROUND(D2,2)is 692.71: two decimal places.=ROUND(D2,0)is 693: the nearest whole number.=ROUND(D2,-1)is 690: the nearest 10.=ROUND(D2,-2)is 700: the nearest 100.
ROUND goes to the nearest value, up or down. Microsoft's own examples show what happens exactly halfway: =ROUND(2.15,1) gives 2.2, and =ROUND(-1.475,2) gives -1.48.
To force a direction, Excel has two sister functions with the same arguments. ROUNDUP always rounds away from zero: =ROUNDUP(3.2,0) is 4. ROUNDDOWN always rounds toward zero: =ROUNDDOWN(3.14159,3) is 3.141. And MROUND rounds to a multiple, such as the nearest 0.5. Those three don't run in the sheets on this page.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee | Hours | Rate | Pay | Nearest 100 | |
| 2 | Avery | 37.75 | 18.35 | 692.71 | 3500 | |
| 3 | Jordan | 40 | 21.4 | 856 | ||
| 4 | Casey | 28.25 | 17.85 | 504.26 | ||
| 5 | Riley | 33.25 | 19.85 | 660.01 | ||
| 6 | Morgan | 38.25 | 20.65 | 789.86 |
-2 rounds that to the nearest hundred. A whole calculation can sit inside ROUND. Try -3 for the nearest thousand: 4000.Rounding is not formatting
In Excel, a number format such as Currency, or the Decrease Decimal button, changes how a number is displayed and leaves the value alone. Microsoft's page on number formats says so directly, and adds that the actual value stays visible in the formula bar. Every formula that points at the cell uses that full value.
The sheets on this page show at most two decimals, like a cell formatted as currency. So the Pay column below looks rounded and isn't: D2 shows 692.71 and holds 692.7125. Add up the five amounts you can see and you get 3502.84. Now look at the total:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee | Hours | Rate | Pay |
| 2 | Avery | 37.75 | 18.35 | 692.71 |
| 3 | Jordan | 40 | 21.4 | 856 |
| 4 | Casey | 28.25 | 17.85 | 504.26 |
| 5 | Riley | 33.25 | 19.85 | 660.01 |
| 6 | Morgan | 38.25 | 20.65 | 789.86 |
| 7 | Total | 3502.85 |
=ROUND(D2,2)+ROUND(D3,2)+ROUND(D4,2)+ROUND(D5,2)+ROUND(D6,2) gives 3502.84.The fix is to round where each amount is calculated: =ROUND(B2*C2,2) in every row of the Pay column. Then what is stored is what is shown and what gets paid, and the total agrees with the lines above it.
The same gap breaks comparisons. 692.7125 is not equal to 692.71, even when both cells show 692.71, so a check like =D2=E2 answers FALSE. Exercise 3 is that case.
Practice: 3 exercises
Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in number won't pass.
In D2, calculate Avery's pay, hours times rate, rounded to 2 decimals.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee | Hours | Rate | Pay |
| 2 | Avery | 37.75 | 18.35 | |
| 3 | Jordan | 40 | 21.4 | |
| 4 | Casey | 28.25 | 17.85 | |
| 5 | Riley | 33.25 | 19.85 | |
| 6 | Morgan | 38.25 | 20.65 |
Show the answer
=ROUND(B2*C2,2) gives 692.71.
The multiplication gives 692.7125. ROUND( … ,2) keeps two decimals and stores 692.71, so totals and comparisons built on this cell use the amount that is paid.
D2 is Avery's pay for one week. In F2, estimate the pay for a year: D2 times 52, rounded to the nearest thousand.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee | Hours | Rate | Pay | Per year | |
| 2 | Avery | 37.75 | 18.35 | 692.71 | ||
| 3 | Jordan | 40 | 21.4 | 856 | ||
| 4 | Casey | 28.25 | 17.85 | 504.26 | ||
| 5 | Riley | 33.25 | 19.85 | 660.01 | ||
| 6 | Morgan | 38.25 | 20.65 | 789.86 |
Show the answer
=ROUND(D2*52,-3) gives 36000.
692.7125 × 52 = 36021.05. With -3, ROUND works three places to the left of the decimal point, so the result is a multiple of 1000: 36000.
The payroll system paid Avery the amount in E2. D2 is the calculated pay, and it shows the same 692.71. In F2, return TRUE when D2 rounded to cents equals E2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee | Hours | Rate | Pay | Paid | Match? |
| 2 | Avery | 37.75 | 18.35 | 692.71 | 692.71 | |
| 3 | Jordan | 40 | 21.4 | 856 | 856 | |
| 4 | Casey | 28.25 | 17.85 | 504.26 | 504.26 | |
| 5 | Riley | 33.25 | 19.85 | 660.01 | 660.01 | |
| 6 | Morgan | 38.25 | 20.65 | 789.86 | 789.86 |
Show the answer
=ROUND(D2,2)=E2 gives TRUE.
ROUND(D2,2) turns 692.7125 into 692.71, and that equals E2. Without ROUND the answer is FALSE, because the two cells only look the same.
Questions people ask
What is the difference between ROUND and formatting a cell to 2 decimals?
Formatting changes the display: the cell shows 692.71 and still holds 692.7125, and formulas use 692.7125. ROUND changes the value: the cell holds 692.71. Use formatting when you only want a cleaner look, and ROUND when the rounded number is the one that has to be added, compared or paid.
How do I round to the nearest 10, 100 or 1000 in Excel?
Give ROUND a negative number of digits: -1 for the nearest 10, -2 for the nearest 100, -3 for the nearest 1000. =ROUND(3502.85,-2) is 3500.
How do I always round up or always round down?
With ROUNDUP and ROUNDDOWN, which take the same two arguments as ROUND. ROUNDUP always rounds away from zero and ROUNDDOWN always toward zero. Microsoft's examples: =ROUNDUP(3.2,0) is 4 and =ROUNDDOWN(3.14159,3) is 3.141.
Why is my Excel total off by one cent?
Because the cells being added hold more decimals than they show. The total is the sum of the stored values, not of the displayed ones. Round each amount where it is calculated, with =ROUND( … ,2), and the total matches the lines. The third live example on this page shows it.