Excel CEILING.MATH Function

Summary

The Excel CEILING.MATH function rounds a number up to a specified multiple. Unlike the CEILING function, CEILING.MATH defaults to a multiple of 1, and provides explicit control over rounding direction for negative numbers.

Purpose

Round a number up to nearest multiple

Return value

A rounded number.

Syntax

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

Arguments

number - The number that should be rounded.
significance - [optional] Multiple to use when rounding. Default is 1.
mode - [optional] For rounding negative numbers toward or away from zero. Default is 0.

Usage notes

The Excel CEILING.MATH function will round a number up to the nearest integer or given multiple of significance. By default, CEILING.MATH rounds to the nearest integer, using a significance of 1. Positive numbers with decimal portions are rounded up to the nearest integer (e.g. 6.3 is rounded up to 7) and negative numbers with decimal portions are rounded toward zero (e.g. -6.3 is rounded up to -6).

=CEILING.MATH(6.3) // returns 7=CEILING.MATH(-6.3) // returns -6

Rounding negative numbers

Control for rounding negative numbers toward zero or away from zero is provided via the (optional) mode argument. Mode defaults to zero. When mode is zero, or omitted, CEILING.MATH rounds negative numbers toward zero. When mode is any non-zero value, CEILING.MATH rounds negative numbers away from zero. Mode has no effect on positive numbers.

=CEILING.MATH(-4.1) // returns -4=CEILING.MATH(-4.1,1) // returns -4=CEILING.MATH(-4.1,1,1) // returns -5=CEILING.MATH(-4.1,1,-1) // returns -5=CEILING.MATH(-4.1,1,TRUE) // returns -5

CEILING.MATH vs CEILING

The CEILING.MATH function differs from the CEILING function in these ways:

CEILING.MATH provides a default multiple of 1 for positive numbers and -1 for negative numbers
CEILING.MATH provides explicit control for rounding negative numbers (toward zero, away from zero)
CEILING.MATH appears to use the absolute value of the significance argument. Changing the sign of significance has no effect on the result.

Notes

To round to the nearest multiple (either up or down) see the MROUND function.
CEILING.MATH works like CEILING, but provides control for rounding direction for negative values.
The mode argument has no effect on positive numbers.
If number is an exact multiple of significance, no rounding occurs.