Rounding functions allow you to round numbers up or down, to even or odd numbers, to the nearest multiple of a number, and to the nearest integer.
Rounding Up and Down
ROUND (number, precision): Rounds the number to the specified number of decimal places (precision). In this case, the number will be rounded up if the digit is greater than or equal to five, and rounded down if the digit is less than five. The precision cannot be omitted.
ROUNDDOWN (number, precision): Rounds the number down to the specified number of decimal places (precision). The precision cannot be omitted. In this case, 1.2 would be rounded to 1, while -1.2 would be rounded to -1.
ROUNDUP (number, precision): Rounds the number up to the specified number of decimal places (precision). The precision cannot be omitted. In this case, 1.2 would be rounded to 2, while -1.2 would be rounded to -2.
CEILING (number1, number2): Rounds number1 up to the nearest multiple of number2.
FLOOR (number1, number2): Rounds number1 down to the nearest multiple of number2.
Examples:
Formula |
Result |
=ROUND(8.5, 0) |
9 |
=ROUND(8.4, 0) |
8 |
=ROUNDDOWN(25, 0) |
25 |
=ROUNDDOWN(25, -1) |
20 |
=ROUNDDOWN(-5.6, 0) |
-5 |
=ROUNDUP(-5.6, 0) |
-6 |
=CEILING(5, 3) |
6 |
=FLOOR(2, 0.75) |
1.5 |
Rounding to Even and Odd Numbers
EVEN (number): Rounds the number up to the nearest even integer. For example, 25 would be rounded to 26.
ODD (number): Rounds the number up to the nearest odd integer. For example, 100 would be rounded to 101.
Rounding to Integers
INT (number): Rounds the number down to the nearest integer. Negative numbers will be rounded to the lower integer. For example, 1.2 would be rounded to 1, while -1.2 would be rounded to -2.
TRUNC (number, precision): Truncates the number to an integer. The number will be rounded down. For example, 1.2 would be rounded to 1, while -1.2 would be rounded to -1. If precision is omitted, the number will be rounded to zero decimal places.