CEILING.MATH Function

Rounds a number up to the nearest integer or multiple.

Category

Math & Trig

Available in

Excel 2013+, Microsoft 365

Returns

A number

Want to practice CEILING.MATH in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

CEILING.MATH is the modern way to round up to a multiple. Leave significance out and it rounds up to the next whole number.

For negative numbers it rounds towards zero by default (−4.3 → −4); set mode to 1 to round away from zero instead.

Syntax

=CEILING.MATH(number, [significance], [mode])

Arguments

ArgumentRequired?What it does
numberYesThe value to round.
significanceOptionalThe multiple to round up to. Default 1.
modeOptionalFor negative numbers: 0 (default) rounds towards zero, any other value rounds away from zero.

Examples

Example 1 — Rounding up to $10 bands

Group order values into $10 price bands.

B2fx=CEILING.MATH(A2, 10)
AB
1Order valueBand
242.550
36060
4-17-10
=CEILING.MATH(A2, 10) Explain

Example 2 — Hours billed

Bill every part-hour as a full hour.

=CEILING.MATH(B2*24) Explain

Common mistakes

  • Expecting −20 for −17. By default negatives round towards zero. Add mode 1: CEILING.MATH(-17, 10, 1) returns −20.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.