Excel ROUND Function
ROUND Function
The ROUND function is a premade function in Excel, which rounds a number to a given number of digits.
It is typed =ROUND and has the following parts:
=ROUND(number, num_digits)
Note: The different parts of the function are separated by a symbol, like comma , or semicolon ;
The symbol depends on your Language Settings.
Number: The number to round. This is usually a cell, like C2.
Num_digits: The number of digits to round to:
| Num_digits | Rounds to | Example | Result |
|---|---|---|---|
2 | 2 decimals | =ROUND(1234.5678, 2) | 1234.57 |
1 | 1 decimal | =ROUND(1234.5678, 1) | 1234.6 |
0 | Nearest whole number | =ROUND(1234.5678, 0) | 1235 |
-1 | Nearest 10 | =ROUND(1234.5678, -1) | 1230 |
-2 | Nearest 100 | =ROUND(1234.5678, -2) | 1200 |
-3 | Nearest 1000 | =ROUND(1234.5678, -3) | 1000 |
A positive num_digits rounds to decimals. 0 rounds to a whole number. A negative num_digits rounds to the left of the decimal point, to tens, hundreds and so on.
Note: Numbers that are exactly halfway are rounded away from zero. =ROUND(2.5, 0) returns 3, and =ROUND(-2.5, 0) returns -3.
How to use the ROUND function:
- Select a cell (
D2) - Type
=ROUND - Double click the ROUND command
- Select the cell with the number to round (
C2) - Type (
,) - Type the number of digits (
0) - Hit enter
Let's have a look at an example!
The Poke Mart has a sale: everything is 12.5% off. These are the normal prices from the Poke Mart product list. Copy them and paste them into cell A1:
Example data
A B 1 Item Price 2 Poke Ball 200 3 Great Ball 600 4 Ultra Ball 1200 5 Potion 300 6 Super Potion 700 7 Revive 1500 8 Antidote 100 9 Awakening 250
Type Sale Price in C1 and Rounded in D1.
Calculate the sale price in C2 with the formula =B2*(1-12.5%), and fill it down to C9. Some sale prices get decimals, like 262.5 and 218.75.
Poke Dollars have no cents, so round the sale prices to whole numbers. Type the ROUND formula in D2 and fill it down to D9:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Price | Sale Price | Rounded |
| 2 | Poke Ball | 200 | 175 | 175 |
| 3 | Great Ball | 600 | 525 | 525 |
| 4 | Ultra Ball | 1200 | 1050 | 1050 |
| 5 | Potion | 300 | 262.5 | 263 |
| 6 | Super Potion | 700 | 612.5 | 613 |
| 7 | Revive | 1500 | 1312.5 | 1313 |
| 8 | Antidote | 100 | 87.5 | 88 |
| 9 | Awakening | 250 | 218.75 | 219 |
=ROUND(C2, 0)
218.75 is rounded to 219. 262.5 is exactly halfway, so it is rounded up to 263. Prices that are already whole numbers, like 175, stay the same.
You can also calculate and round in one step: =ROUND(B2*(1-12.5%), 0)
Round to Tens and Hundreds
Use a negative num_digits to round to tens or hundreds. -1 rounds to the nearest 10, and -2 rounds to the nearest 100. This is useful for simple, even prices.
Type Nearest 10 in E1 and Nearest 100 in F1. Then type the formulas in E2 and F2, and fill them down to row 9:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Price | Sale Price | Rounded | Nearest 10 | Nearest 100 |
| 2 | Poke Ball | 200 | 175 | 175 | 180 | 200 |
| 3 | Great Ball | 600 | 525 | 525 | 530 | 500 |
| 4 | Ultra Ball | 1200 | 1050 | 1050 | 1050 | 1100 |
| 5 | Potion | 300 | 262.5 | 263 | 260 | 300 |
| 6 | Super Potion | 700 | 612.5 | 613 | 610 | 600 |
| 7 | Revive | 1500 | 1312.5 | 1313 | 1310 | 1300 |
| 8 | Antidote | 100 | 87.5 | 88 | 90 | 100 |
| 9 | Awakening | 250 | 218.75 | 219 | 220 | 200 |
The formula in E2:
=ROUND(C2, -1)
The formula in F2:
=ROUND(C2, -2)
175 is exactly halfway between 170 and 180, so it is rounded up to 180. For the same reason, 1050 is rounded up to 1100 in column F.
218.75 is rounded to 220 in column E, but to 200 in column F, because 218.75 is closer to 200 than to 300.
ROUNDUP and ROUNDDOWN
ROUND goes to the nearest value. If you always want to round in one direction, use ROUNDUP or ROUNDDOWN. They have the same parts as ROUND.
ROUNDUP always rounds away from zero. The Super Potion sale price in C6 is 612.5. ROUND to the nearest 10 gives 610, but ROUNDUP gives 620:
=ROUNDUP(C6, -1)
ROUNDDOWN always rounds toward zero. The Awakening sale price in C9 is 218.75. ROUND to a whole number gives 219, but ROUNDDOWN gives 218:
=ROUNDDOWN(C9, 0)
ROUND vs. Number Formats
You can also hide decimals with a number format, like the Decrease Decimal button. See Number Formats.
A number format only changes how the number looks. The value in the cell stays the same. ROUND changes the value itself.
This can make totals look wrong. Format C2:C10 to show no decimals, and add a total in row 10 with SUM:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Price | Sale Price | Rounded |
| 2 | Poke Ball | 200 | 175 | 175 |
| 3 | Great Ball | 600 | 525 | 525 |
| 4 | Ultra Ball | 1200 | 1050 | 1050 |
| 5 | Potion | 300 | 263 | 263 |
| 6 | Super Potion | 700 | 613 | 613 |
| 7 | Revive | 1500 | 1313 | 1313 |
| 8 | Antidote | 100 | 88 | 88 |
| 9 | Awakening | 250 | 219 | 219 |
| 10 | Total | 4244 | 4246 |
The formula in C10:
=SUM(C2:C9)
The formula in D10:
=SUM(D2:D9)
Columns C and D look the same, but the totals are different. C10 adds the real values, which are 4243.75 in total. The format shows it as 4244. D10 adds the rounded values, which gives 4246.
Note: Use ROUND when you want to calculate with the rounded value, like the price a customer pays. Use a number format when you only want the number to look shorter.
