- dotTech - https://dottech.org -

How to use CEILING functions in Excel spreadsheets [Tip]

CEILING is an Excel 2010/13 function that you can add to spreadsheets to find ceiling values. The function shows you the ceiling value of a number based on a specific multiple. For example, the ceiling value of five based on a multiple of two would be six.

As an example, enter 55 into a spreadsheet cell. Then select a cell to add the function to. Click the fx button and select CEILING to open the window shown in the shot below.

[1]

Select the cell with 55 in it by pressing the button beside the Number field. In the Significance field below that enter the multiple to round to. Enter 9 in that text box.

Click OK to close the window. The CEILING function will now include a value of 63 in it. Thus, it searched for the nearest integer that is a multiple of nine. The nearest values are 54 and 63. The CEILING function shows the nearest highest value.

[2]

Alternatively, you can find ceiling values exclusively without selecting cells. Click a cell to add the function to, and enter =CEILING(55, 9) in the function bar. That will find the CEILING for 55 based on a multiple of nine.

[3]

To find the ceiling values of decimal numbers, enter 1 as the Significance value. Then the CEILING cell will show you the nearest whole number ceiling value as below.

[4]

So now you can round up numbers to multiples of significance or a number of decimal places in Excel 2010/13 spreadsheets. This YouTube page [5] includes a video that shows you how to add the CEILING function to Excel 2010/13 spreadsheets.