In Excel, rounding functions are used to round numbers to a specified number of decimal places or significant digits. Below are 10 rounding functions, including examples with data and formulas:
1. ROUND
Rounds a number to a specified number of digits.
Formula:
=ROUND(number, num_digits)
Example:
=ROUND(3.14159, 2) // Returns 3.14
2. ROUNDUP
Rounds a number up, away from zero.
Formula:
=ROUNDUP(number, num_digits)
Example:
=ROUNDUP(3.14159, 2) // Returns 3.15
3. ROUNDDOWN
Rounds a number down, toward zero.
Formula:
=ROUNDDOWN(number, num_digits)
Example:
=ROUNDDOWN(3.14159, 2) // Returns 3.14
4. MROUND
Rounds a number to the nearest multiple of a specified value.
Formula:
=MROUND(number, multiple)
Example:
=MROUND(7, 3) // Returns 6 (nearest multiple of 3)
5. CEILING
Rounds a number up to the nearest multiple of significance.
Formula:
=CEILING(number, significance)
Example:
=CEILING(4.3, 0.5) // Returns 4.5
6. FLOOR
Rounds a number down to the nearest multiple of significance.
Formula:
=FLOOR(number, significance)
Example:
=FLOOR(4.7, 0.5) // Returns 4.5
7. INT
Rounds a number down to the nearest integer.
Formula:
=INT(number)
Example:
=INT(3.9) // Returns 3
8. TRUNC
Truncates a number to a specified number of decimal places (no rounding).
Formula:
=TRUNC(number, [num_digits])
Example:
=TRUNC(3.14159, 2) // Returns 3.14
9. EVEN
Rounds a number up to the nearest even integer.
Formula:
=EVEN(number)
Example:
=EVEN(3.2) // Returns 4
10. ODD
Rounds a number up to the nearest odd integer.
Formula:
=ODD(number)
Example:
=ODD(3.2) // Returns 5
Example Data and Formulas in a Table:
| Number | Formula | Result |
|---|---|---|
| 3.14159 | =ROUND(A2, 2) | 3.14 |
| 3.14159 | =ROUNDUP(A3, 2) | 3.15 |
| 3.14159 | =ROUNDDOWN(A4, 2) | 3.14 |
| 7 | =MROUND(A5, 3) | 6 |
| 4.3 | =CEILING(A6, 0.5) | 4.5 |
| 4.7 | =FLOOR(A7, 0.5) | 4.5 |
| 3.9 | =INT(A8) | 3 |
| 3.14159 | =TRUNC(A9, 2) | 3.14 |
| 3.2 | =EVEN(A10) | 4 |
| 3.2 | =ODD(A11) | 5 |
These functions are useful for various scenarios, such as financial calculations, data analysis, and formatting numbers for reports.
No comments:
Post a Comment