CEILING Function (LibreOffice Calc)
The CEILING function in LibreOffice Calc rounds a number up to the nearest multiple of a specified significance. Learn syntax, examples, common errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the CEILING Function Does ▾
- Rounds a number up to the nearest multiple of a significance
- Works with positive and negative numbers
- Supports fractional and whole-number significance values
- Useful for pricing, scheduling, and interval-based calculations
- Works with cell references, formulas, and expressions
It is designed to be precise, predictable, and universally compatible.
Syntax ▾
CEILING(number; significance)
Arguments
-
number:
The numeric value to round up. -
significance:
The multiple to round up to.
Examples:1→ nearest whole number0.5→ nearest half10→ nearest ten0.01→ nearest cent
Basic Examples ▾
Round up to nearest whole number
=CEILING(5.1; 1)
Returns 6.
Round up to nearest 0.5
=CEILING(5.1; 0.5)
Returns 5.5.
Round up to nearest 10
=CEILING(123; 10)
Returns 130.
CEILING with a cell reference
=CEILING(A1; 0.25)
Rounds A1 up to the nearest quarter.
Advanced Examples ▾
CEILING with negative numbers
=CEILING(-5.2; 1)
Returns -5.
CEILING with fractional significance
=CEILING(7.34; 0.1)
Returns 7.4.
CEILING for pricing
=CEILING(A1; 0.05)
Rounds prices up to the nearest 5 cents.
CEILING for scheduling
=CEILING(A1; 0.25)
Rounds time up to the nearest 15 minutes.
CEILING for binning/grouping
=CEILING(A1; 10)
Groups values upward into buckets of 10.
CEILING vs ROUNDUP comparison
=CEILING(A1; 1)
Equivalent to ROUNDUP(A1; 0) for positive numbers.
Common Errors and Fixes ▾
CEILING returns unexpected results with negative numbers
Cause:
- CEILING always rounds toward positive infinity, not away from zero.
Example:
CEILING(-5.2; 1) → -5
CEILING returns Err:502
Occurs when:
- significance is zero
- significance has a different sign than number
- number or significance is non-numeric
Fix:
Ensure both values are numeric and have compatible signs.
CEILING returns 0 unexpectedly
Possible causes:
- significance is larger than the number
- number is between -significance and significance
- number is stored as text
Fix:
Convert text to numbers using:
Data → Text to Columns → OK
Best Practices ▾
- Use CEILING when rounding must always go upward
- Use FLOOR when rounding must always go downward
- Use ROUND for standard rounding
- Use ROUNDUP for rounding away from zero
- Use CEILING for pricing, scheduling, and capacity planning
Related Patterns and Alternatives ▾
- Use FLOOR to round down to a significance
- Use ROUND, ROUNDUP, or ROUNDDOWN for decimal rounding
- Use INT for rounding down to the nearest integer
- Use TRUNC to remove decimals without rounding
- Use MOD with CEILING for bucket indexing
By mastering CEILING and its combinations with other math functions, you can build precise, significance-based numeric models in LibreOffice Calc that behave consistently across all data scenarios.