FormuladrillROUND
ROUND(number, num_digits) rounds a number to the given number of decimal places, with 0.5 rounding away from zero. Use 0 for a whole number and a negative number to round to tens or hundreds. ROUNDUP and ROUNDDOWN always move away from or toward zero, and MROUND rounds to a multiple.
Syntax
ROUND(number, num_digits) ROUNDUP(number, num_digits) ROUNDDOWN(number, num_digits) MROUND(number, multiple)| Part | What it is |
|---|---|
number | The value or formula to round. |
num_digits | Decimal places to keep: 2 for cents, 0 for a whole number, -2 for the nearest hundred. |
multiple (MROUND) | The step to round to, such as 5 or 0.25. |
Examples
=ROUND(3.14159, 2)Result: 3.14
=ROUND(1234.567, -2)Result: 1200
Negative digits round to the left of the decimal point.
=ROUNDUP(2.301, 1)Result: 2.4
ROUNDUP always moves away from zero; ROUNDDOWN(2.399, 1) gives 2.3.
=MROUND(17, 5)Result: 15
MROUND(18, 5) gives 20. The number and multiple must have the same sign.
Common mistakes
- Totals are off by a cent: the cells are formatted to 2 decimals but still hold the full value. Wrap the formula in ROUND(..., 2) so the stored value is rounded.
- ROUND(2.5, 0) gives 3 and ROUND(-2.5, 0) gives -3; Excel rounds halves away from zero, not to the even number.
- Using INT to round down negative numbers: INT(-2.3) is -3. Use ROUNDDOWN(-2.3, 0) for -2 or TRUNC.
- MROUND(-17, 5) returns #NUM! because the signs differ; use MROUND(-17, -5).
Practice ROUND
These drills check your answer on live data, free, no account needed.
- Round to centsWork out each price including the sales tax in F1, rounded to 2 decimals. Fill C2 and C3.
Questions
How do I round to 2 decimal places in Excel?
=ROUND(A2, 2). To always round up to the next cent use ROUNDUP(A2, 2), to always cut use ROUNDDOWN(A2, 2).
How do I round to the nearest 5 or 10?
=MROUND(A2, 5) for the nearest 5. For the nearest 10, =ROUND(A2, -1) or =MROUND(A2, 10). To round up to the next 10 use CEILING(A2, 10).
Why does Excel show 0.30 but the sum is wrong?
The display is rounded, the value is not. Either round the values with ROUND or turn on Set precision as displayed in the options (which changes the stored values permanently).
Related
- MODHow the Excel MOD function returns the remainder of a division, with examples for odd and even, every nth row, the decimal part of a number and the sign rule.
- ABSHow the Excel ABS function returns a number without its sign, with examples for the gap between two cells, a tolerance check and making negatives positive.
- TEXTHow the Excel TEXT function turns a number or date into text in a format you choose, with examples for dates, thousand separators, percentages and padding.
- SUMPRODUCTHow the Excel SUMPRODUCT function multiplies ranges and sums the result, with examples for quantity times price, weighted average and sums with conditions.