Changing Data With the CEILING Function
Like other rounding functions, the CEILING function alters the data in your worksheet and, therefore, affects the results of any calculations that use the rounded values. As an alternative, Excel has formatting options that allow you to change the number of decimal places displayed by your data without changing the numbers themselves. Making formatting changes to data does not affect calculations.
Excel CEILING Function
A function’s syntax refers to the function’s layout and includes its name, brackets, and arguments. The syntax for the CEILING function is: = CEILING (Number, Significance) Number — The value to round. This argument can contain the actual data for rounding, or it can be a cell reference to the location of the data in the worksheet. Significance —The number of decimal places present in the argument indicates the number of decimal places or significant digits present in the result (rows 2 and 3 of the example).
The function rounds the Number argument specified up to the nearest multiple of this value.If an integer is used, all decimal places are removed, and the result is rounded up to the nearest multiple of this value (see row 4 of the example).For negative Number arguments and positive Significance arguments, the results are rounded upward toward zero (see rows 5 and 6 of the example).For negative Number arguments and negative Significance arguments, the results are rounded downward away from zero (see row 7 of the example).
CEILING Function Example
You can enter the CEILING function by typing the function name and arguments into the desired cell, or using the function’s dialog box as outlined. When you click on cell E1, the complete function = CEILING (A2, 0.1) appears in the formula bar above the worksheet. How Excel arrives at this answer is that:
First, it removes one insignificant digit (2) from the end of the number. One decimal place in the Significance argument means only one decimal place in the result.Next, it rounds the remaining digit of the number up to 34.3 since this is the next highest multiple of 0.10 after 34.2.
Cell C3 to C7 Results
When you repeat the above steps for cells C3 to C7, you obtain the following results:
Cell C3 contains the value 34.25 because the two decimal places in the Significance argument require two decimal places in the result, and 34.25 is the next highest multiple of 0.05 after 34.22.Cell C4 contains the value 35. Because the Significance argument is an integer, all decimal places are removed from the result, and 35 is the next highest multiple of 1 after 34.Cell C5 contains the value -34.2 for the same reasons given for cell C2.Cell C6 contains the value -34 for the same reasons as cell C4.Cell C7 contains the value -35. Combining a negative Number argument and a negative integer for the Significance argument removes all decimal places and rounds the result down to the next multiple of 1 after -34.
