CEILING Function in Microsoft Excel, Google Sheets, OpenOffice Calc, and Apple Numbers returns an integer to a round number to a specific interval or a certain multiple.
To use the CEILING function, you’ll need two inputs: the number you want to round up and the significance or multiple to which you want to round it.
For example, if the price of a product is $5.53 you don’t want to display the prices in pennies. To avoid using pennies in your prices and use the formula =CEILING(5.53,0.05) to round prices up to the nearest nickel.
Up to 2 or 3 numbers, we can round up the values manually. But, when the data set is large then finding rounding off manually will be a tiresome process.
By using the CEILING Function, you can simplify the calculations by rounding up especially, where you want to work with whole numbers. As a result, it is capable of analyzing massive datasets, performing statistical analysis, comparing values quickly, and performing budgeting.
Moreover, this function falls under the statistical function category in spreadsheets. It can be easily used in spreadsheets for mathematical programs.
You can also check out other Spreadsheet Functions like MAX Function, MIN Function, NUMBERTEXT, SpellNumber, and SpellNumber Indian Rupees on our Functions Page.
So, let us figure out how to use the CEILING Function in each spreadsheet program.
Table of Contents
How To Use the CEILING Function in Microsoft Excel?
To use the CEILING function in Excel, follow these steps:
- Select the cell where you want the result of the CEILING calculation to appear.
- Type the following formula into the selected cell: =CEILING(number, significance)
- Replace “number” with the value that you want to round up.
- Replace “significance” with the multiple to which you want to round the number.
- Press Enter to apply the formula and calculate the rounded-up value.
For example, let’s say you have the number 15 in cell A1, and you want to round it up to the nearest multiple of 5. In cell B1, you would enter the formula =CEILING(A1, 5).
After pressing Enter, Excel will evaluate the value in A1, which is 15, and round it up to the nearest multiple of 5, which is 20. The result will appear in cell B1 as 20.
You can do the same for integers also. Let’s say you have a range of values in column A, and you want to round them up to the nearest 0.5 increment. In cell B1, you can use the CEILING function with the formula:
=CEILING(A1, 0.5)
This will round up the value in cell A1 to the nearest 0.5 increments. Copy the formula down to apply it to the rest of the cells in column B, and you will have all the values rounded up to the nearest 0.5 increments.
You can apply the same formula to other cells by copying the formula and pasting it into the desired cells, or by dragging the fill handle of the selected cell down or across to populate the formula to other cells in a desired range.
Remember to adjust the cell references and significance values based on your specific data and requirements.
Syntax
=CEILING(number, significance)
Where:
- “number” is the value that you want to round up.
- “significance” is the multiple to which you want to round the number.
How To Use CEILING Function in OpenOffice Calc?
To use the CEILING function in OpenOffice Calc, follow these steps:
- Select the cell where you want the result of the CEILING calculation to appear.
- Type the following formula into the selected cell: =CEILING(number; significance)
- Replace “number” with the value that you want to round up.
- Replace “significance” with the multiple to which you want to round the number.
- Press Enter to apply the formula and calculate the rounded-up value.
Make sure you separate each cell or the selected range of the cells with a semi-colon (,😉 instead of (:) colon. After adding the value with the semicolon simply close the formula with a closing parenthesis ()) and press Enter.
For example, let’s say you have the number 15 in cell A1, and you want to round it up to the nearest multiple of 5. In cell B1, you would enter the formula =CEILING(A1; 5).
After pressing Enter, OpenOffice Calc will evaluate the value in A1, which is 15, and round it up to the nearest multiple of 5, which is 20. The result will appear in cell B1 as 20.
You can apply the same formula to other cells by copying the formula and pasting it into the desired cells, or by dragging the fill handle of the selected cell (B1) down or across to populate the formula to other cells in a desired range.
Remember to adjust the cell references and significance values based on your specific data and requirements.
Syntax
=CEILING(number; significance)
Where:
- “number” is the value that you want to round up.
- “significance” is the multiple to which you want to round the number.
How To Use the CEILING Function in Google Sheets?
To use the CEILING function in Google Sheets, follow these steps:
- Open a Google Sheets spreadsheet and select the cell where you want the result of the CEILING calculation to appear.
- Type the following formula into the selected cell: =CEILING(number, significance)
- Replace “number” with the value that you want to round up.
- Replace “significance” with the multiple to which you want to round the number.
- Press Enter to apply the formula and calculate the rounded-up value.
For example, let’s say you have the number 15 in cell A1, and you want to round it up to the nearest multiple of 5. In cell B1, you would enter the formula =CEILING(A1, 5).
After pressing Enter, Google Sheets will evaluate the value in A1, which is 15, and round it up to the nearest multiple of 5, which is 20. The result will appear in cell B1 as 20.
You can apply the same formula to other cells by dragging the fill handle of the selected cell (B1) down or across to populate the formula to other cells in a desired range.
Syntax
=CEILING(number, significance)
Where:
- “number” is the value that you want to round up.
- “significance” is the multiple to which you want to round the number.
How To Use The CEILING Function in Apple Numbers
To use the CEILING function in Apple Numbers, follow these steps:
- Select the cell where you want the result of the CEILING calculation to appear.
- Type the following formula into the selected cell: =CEILING(number, significance)
- Replace “number” with the value that you want to round up.
- Replace “significance” with the multiple to which you want to round the number.
- Press Enter or Return to apply the formula and calculate the rounded-up value.
For example, let’s say you have the number 15 in cell A1, and you want to round it up to the nearest multiple of 5. In cell B1, you would enter the formula =CEILING(A1, 5).
After pressing Enter or Return, Numbers will evaluate the value in A1, which is 15, and round it up to the nearest multiple of 5, which is 20. The result will appear in cell B1 as 20.
You can apply the same formula to other cells by copying the formula and pasting it into the desired cells, or by dragging the fill handle of the selected cell (B1) down or across to populate the formula to other cells in a desired range.
Remember to adjust the cell references and significance values based on your specific data and requirements.
Syntax
=CEILING(number, significance)
Where:
- “number” is the value that you want to round up.
- “significance” is the multiple to which you want to round the number.
Possible Errors
#VALUE! error: This error occurs if the supplied arguments are not valid. The number and significance arguments must be numeric values. If any of the arguments are non-numeric or cannot be interpreted as numbers, Excel will display this error.
#NUM! error: This error occurs if the number or significance arguments are less than or equal to zero. The CEILING function requires both arguments to be positive numbers.
#DIV/0! error: This error occurs if the number argument is zero. Since the CEILING function requires a non-zero number, if you provide a zero as the number argument, Excel will display this error.
It’s important to ensure that the arguments provided to the CEILING function are valid and appropriate for the desired rounding operation. To avoid these errors, double-check the input values and ensure they meet the requirements of the CEILING function.
In addition to these errors, keep in mind that the CEILING function always rounds up to the nearest multiple, which may not be suitable for all scenarios. If you need different rounding behavior, such as rounding down or to the nearest multiple, you can consider using other functions like FLOOR or MROUND in combination with CEILING to achieve the desired rounding logic.
If an error occurs, you can use Excel’s error-handling techniques, such as IFERROR or ISERROR functions, to handle or display custom error messages instead of the default error codes.
Don’t forget to share this informative article with your friends and colleagues on various social media platforms like Instagram and Facebook to let them know about its usefulness. Remember, sharing is caring and it is the right way to help others.
Moreover, if you have any doubts or suggestions feel free to write in the comment box below. We will get back to you soon.