Two decimals
| A | |
|---|---|
| 1 | Value |
| 2 | 3.14159 |
Formula: C1: =ROUND(A2,2)
Result: 3.14
Keeps two decimal places.
Math
ROUND
Rounds to the nearest value. Negative digits round to tens, hundreds.
Syntax
ROUND(number, num_digits)Example: =ROUND(1234.567,2)
Available in: All versions
ROUND rounds a number to a set number of digits: =ROUND(number, num_digits). Positive digits keep decimals, 0 gives a whole number, and negative digits round to tens, hundreds or thousands. Halves round away from zero.
When to use it
- You need prices, taxes or percentages stored with exactly two decimals.
- You want totals that add up exactly to what is displayed.
- You round large figures to the nearest hundred or thousand for a summary.
- You need to round up or down always: use ROUNDUP or ROUNDDOWN.
Arguments explained
- number. The value to round: a number, a reference or a calculation.
- num_digits. How many decimal places to keep. Positive values round to the right of the decimal point, 0 rounds to a whole number, and negative values round to the left, so -2 rounds to the nearest hundred.
- Direction. Halves round away from zero. For always up or always down, use ROUNDUP and ROUNDDOWN instead.
Worked examples
Nearest hundred
| A | |
|---|---|
| 1 | Value |
| 2 | 1234.567 |
Formula: C1: =ROUND(A2,-2)
Result: 1200
A negative num_digits rounds to the left of the decimal point.
Price with 8.25% tax
| A | |
|---|---|
| 1 | Price |
| 2 | 19.99 |
Formula: C1: =ROUND(A2*1.0825,2)
Result: 21.64
19.99 × 1.0825 = 21.639175, rounded to cents.
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in D2: =ROUND(A2,2)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Number | Step | Result | |
| 2 | 1234.567 | 50 | 1234.57 | |
| 3 | 17 | 5 | ||
| 4 | -4.7 | 12 | ||
| 5 | 144 | 9 | ||
| 6 | 2.5 | 3 | ||
| 7 | 50 | 12 |
Result in D2: 1234.57
Common errors and how to fix them
- Display versus value. Number formatting only changes how a number looks. The cell still holds the long value. Use ROUND to change the value itself.
- Totals off by a cent. Summing unrounded values and rounding the total differs from summing rounded values. Decide which your process needs.
- Midpoint surprises. 2.5 rounds to 3 and -2.5 to -3 (away from zero). This differs from banker's rounding in some systems.
- #VALUE!. The input is text. Convert it to a number first.
Related functions
Frequently asked questions
- How do I round to the nearest 5 or 50?
- Use MROUND(number, 5).
- How do I always round up?
- Use ROUNDUP, or CEILING.MATH for multiples.
- Why is the result still showing many decimals?
- The cell format may be forcing it; check that the cell is not formatted as Text, and that the formula is ROUND, not just a format.
- Does ROUND change the original cell?
- No, it returns a new value.