The CEILING and FLOOR functions can be used to round up or down The CEILING and FLOOR functions can be used to round up or down a number or date/time to a specified standard value.
This makes it possible to create frequency distributions, round up to the nearest 15 minutes in work time charts, etc.
How it works
Excel 2013 or later
Round up (CEILING.MATH)
=CEILING.MATH(Number,Significance,Mode)
| Parameter | Omission | Description |
|---|---|---|
| Number | Required field.Specify the number to be round up. | |
| Significance | Closest integer. | Specify the base value for round up. |
| Mode | 0 | When "Argument 1: Number" is a negative number, specifies whether the value should be close to or away from 0. - If 0 is specified, the value is close to 0. - If non-zero is specified, the value is far from 0. Inverse of FLOOR.MATH. |
Round down (FLOOR.MATH)
=FLOOR.MATH(Number,Significance,Mode)
| Parameter | Omission | Description |
|---|---|---|
| Number | Required field.Specify the number to be round down. | |
| Significance | Closest integer. | Specify the base value for round down. |
| Mode | 0 | When "Argument 1: Number" is a negative number, specifies whether the value should be close to or away from 0. - If 0 is specified, the value is far from 0. - If non-zero is specified, the value is close to 0. Inverse of CEILING.MATH. |
Excel 2010 and earlier
This function is deprecated for compatibility reasons, although it is still available in the latest version.
Round up (CEILING)
=CEILING(Number,Significance)
| Parameter | Omission | Description |
|---|---|---|
| Number | Required field. Specify the number to be round up. | |
| Significance | Required field. Specify the base value for round up. |
Round down (FLOOR)
=FLOOR(Number,Significance)
| Parameter | Omission | Description |
|---|---|---|
| Number | Required field. Specify the number to be round down. | |
| Significance | Required field. Specify the base value for round down. |
Demonstrate
The following is an example of a pattern of values given as parameters and results.
Spill
Spill if the parameter is a cell range.
---