Here is how the CEILING and FLOOR functions work in Excel:
Previously we looked at the MOD function in Excel. To review that post, click HERE.
The CEILING and FLOOR functions in Excel are somewhat complimentary to the MOD function. Here is what Excel says about each:
MOD – returns a remainder after a number is divided by a divisor =MOD(number,divisor)
CEILING – rounds a number up to the nearest multiple of significance =CEILING(number,significance)
FLOOR – rounds a number down to the nearest multiple of significance =FLOOR(number,significance)
In the previous post about MOD, we had quantities of inventory and packaging amounts, and when dividing the inventory amount by the packaging quantity, the result of the formula was the remainder, or what was left over after we had packaged up the full quantities:
We can use CEILING and FLOOR in a similar way. Here I’ve shown the CEILING function in column G and what the difference between the result of that and the Qty is. Note that the remainder in column F provided by the MOD function and the amount in column H add up to the Pkg Amt:
In a similar way, here is the FLOOR function in column I. In column J I’ve shown the difference between the FLOOR function result and the Qty. Notice how it is exactly the same as the MOD function result:
So, depending on how you want to evaluate your inventory and the remaining quantities, or know how much to order from your supplier in order to have full packaging quantities, you can decide which you would prefer – MOD, CEILING, or FLOOR.