All about Ceiling() and Floor() Excel rounding options
Excel’s Ceiling() and Floor() function rounds a number up or down to the nearest specified multiple. The most common use is to match prices to denominations of coins or notes.
Ceiling() is similar to MRound() except that MRound() rounds to the nearest multiple, Ceiling() always rounds UP. The reverse is Floor() which always rounds down.
There’s also RoundUP() or RoundDOWN() which will round up/down, but only for decimal places (like Round() does ) not specific multiples.
There’s also newer Ceiling.Math() and Floor.Math() that are mostly the same but have an extra option.
Ceiling() basics
The basics of Ceiling() function are:
Ceiling(number, significance)
Number is the value that you want to round up.
Significance is the multiple to which you want to round up.
Ceiling() has been in Excel since at least Excel 2007 for Windows and Excel 2011 for Mac.
It’s modern name is Ceiling.Math() but the old Ceiling() name still works.
Ceiling() examples
If you have a value of 27.2 and you want to round it up to the nearest multiple of 5, you would use the formula =CEILING(27.2,5), which would return 30.
Similarly, if you have a value of 12.6 and you want to round it up to the nearest multiple of 2, you would use the formula =CEILING(12.6,2), which would return 14.
The ‘extra’ Ceiling.Math() option for negative numbers
Ceiling.Math() has a third optional parameter which only applies for NEGATIVE numbers. It controls the ‘direction’ of rounding for values below zero.
Microsoft calls this option ‘Mode’, perhaps the most generic and unhelpful name possible. ‘Mode’ is only possible in Ceiling.Math() not the older Ceiling() function.
Normally, Ceiling.Math() will round negative numbers AWAY from zero (i.e. to a lower value) e.g. -3.1 will round to -4. But sometimes you need negative rounding the other way, TOWARDS zero (a higher value). That’s where Mode comes in.
Mode/ direction options with Ceiling.Math()
0 Zero – is the default, rounding TO zero (a higher number),.
-1 (or any negative number) – rounds AWAY from zero (a lower number).
Floor() in Excel
Excel’s Floor() function rounds a number DOWN to the nearest specified multiple. It’s most common use it rounding prices to match denominations of coins or notes.
Floor() is similar to MRound() except that MRound() rounds to the nearest multiple, Floor() always rounds DOWN. The reverse is Ceiling() which always rounds up.
There’s also RoundDOWN() which will round down, but only for decimal places (like Round() ) not specific multiples.
Floor() basics
The basics of Floor() function is:
Floor(number, significance)
Number is the value that you want to round down.
Significance is the multiple to which you want to round down.
The “number” argument is the number you want to round down, and the “significance” argument is the multiple to which you want to round down. For example, if you want to round down a number to the nearest multiple of 5, you would use 5 as the significance value.
Floor() has been in Excel since at least Excel 2007 for Windows and Excel 2011 for Mac.
It’s modern name is Floor.Math() but the old Floor() name still works.
Floor() examples
The FLOOR function always rounds down, even if the number is closer to the next multiple than to the specified multiple. For example, if you use the FLOOR function to round down 12.7 to the nearest multiple of 5, the result will be 10, not 15.
If you have a value of 27.8 and you want to round it down to the nearest multiple of 5, you would use the formula =FLOOR(27.8,5), which would return 25.
Similarly, if you have a value of 13.6 and you want to round it down to the nearest multiple of 2, you would use the formula =FLOOR(13.6,2), which would return 12.
The ‘extra’ Floor.Math() option for negative numbers
Floor.Math() has a third optional parameter which only applies for NEGATIVE numbers. It controls the ‘direction’ of rounding for values below zero.
Microsoft calls this option ‘mode’, perhaps the most generic and unhelpful name possible. ‘Mode’ is only possible in Floor.Math() not the older Floor() function.
Normally, Floor.Math() will round negative numbers AWAY from zero (i.e. to a lower value) e.g. -3.1 will round to -4. But sometimes you need negative rounding the other way, TOWARDS zero (a higher value). That’s where Mode comes in.
Mode /direction options with Floor.Math()
0 Zero – is the default, rounding away from zero
-1 (or any negative number) – rounds TOWARDS zero.
All about MROUND() in Excel
Excel’s Round () and five other rounding functions