ExcelMathBeginner

CEILING.MATH

Rounds a number up to the nearest integer or to the nearest multiple of significance, regardless of the sign of the number.

Read the syntaxReview worked examplesOpen the spreadsheet app
=CEILING.MATH(number, [significance], [mode])
Open Spreadsheet Explorer

This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.

What the function does

Overview

Rounds a number up to the nearest integer or to the nearest multiple of significance, regardless of the sign of the number.

CEILING.MATH provides more control over rounding behavior, especially with negative numbers, compared to the older CEILING function. It rounds a number up, away from zero, to the nearest multiple of `significance`. If `significance` is omitted, it defaults to 1. The optional `mode` argument allows you to specify whether negative numbers should be rounded towards or away from zero. If `mode` is 0 or omitted, negative numbers are rounded up (away from zero). If `mode` is any non-zero value, negative numbers are rounded towards zero. This flexibility makes CEILING.MATH suitable for financial calculations or scenarios where consistent rounding behavior across positive and negative values is critical. For example, CEILING.MATH(-2.3, 0.5, 1) would return -2.0 (towards zero), while CEILING.MATH(-2.3, 0.5) or CEILING.MATH(-2.3, 0.5, 0) would return -2.5 (away from zero).

Quick reference

Syntax

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

Inputs

Arguments

numberThe number you want to round.
Example: A2
significance (optional)The multiple to which you want to round. Defaults to 1 if omitted.
Example: 0.1
mode (optional)A value that specifies whether to round negative numbers toward or away from zero. If 0 or omitted, rounds away from zero. If any non-zero value, rounds toward zero.
Example: 1

Formula patterns

Examples

1

Round Up Positive Number

=CEILING.MATH(4.3, 1)
roundingpositive numbers
2

Round Up Negative Number (towards zero)

=CEILING.MATH(-4.3, 1, 1)
roundingnegative numbersmode

Avoid these issues

Common Errors

1

#VALUE!

Cause: One or more arguments are non-numeric.

Fix: Ensure 'number', 'significance', and 'mode' (if used) are valid numeric values or references to cells containing numbers.

2

Unexpected Result

Cause: Misunderstanding of how the 'mode' argument affects negative numbers.

Fix: Carefully consider whether negative numbers should round towards or away from zero and set the 'mode' argument accordingly (0 or omitted for away, non-zero for towards).

Platform support

Compatibility

Excel 2013+Google Sheets

Introduced in Excel 2013. Recommended over CEILING for improved flexibility with negative numbers.

Source: Microsoft Support

Common questions

Frequently Asked Questions

Rounds a number up to the nearest integer or to the nearest multiple of significance, regardless of the sign of the number.

number: The number you want to round. significance: The multiple to which you want to round. Defaults to 1 if omitted. mode: A value that specifies whether to round negative numbers toward or away from zero. If 0 or omitted, rounds away from zero. If any non-zero value, rounds toward zero.

#VALUE!: Ensure 'number', 'significance', and 'mode' (if used) are valid numeric values or references to cells containing numbers. Unexpected Result: Carefully consider whether negative numbers should round towards or away from zero and set the 'mode' argument accordingly (0 or omitted for away, non-zero for towards).