Excel FLOOR.MATH function - Returns a Number Rounded Down to the Nearest Level of Significance

The FLOOR.MATH function works likes the CEILING.MATH function except that the FLOOR.MATH function returns a number rounded down to the nearest multiple of significance rather than rounded up.

The Syntax for the FLOOR.MATH function is:
    FLOOR.MATH(number, [significance], [mode]

Argument Descriptions:
    Number is the value to be rounded down (towards the floor)
    Significance is the multiple to which the Number is to be rounded.
    Mode only affects Negative numbers. If mode is anything other than zero, the Number arugment will be rounded away from zero. .

The FLOOR.MATH function rounds a number down to the nearest multiple of significance unless the number is negative and the mode argument is used and it is a non-zero number.

The significance and mode arguments are optional. If only the number argument is used the FLOOR.MATH function will make the number go down (to the floor) to the next integer.

Excel FLOOR.MATH Table 1

Lowered to the nearest penny:
FLOOR.MATH lowered to nearest penny

Lowered to the nearest nickel:
Excel FLOOR.MATH lowered to the nearest nickel

Lowered to the nearest quarter:
Excel FLOOR.MATH lowered to the nearest quarter

Lowered to the nearest hundred dollars:
Excel FLOOR.MATH lowered to nearest hundred

Lowered to the nearest thousand dollars:
FLOOR.MATH lowered to nearest thousand

Use FLOOR.MATH to determine the number of complete sets.

The FLOOR.MATH function can also be used to determine how many complete sets there are in a given number.  For example, how many full dozens are there in 28, how many full grosses are there in 300, how many full days are there in 125 hours, etc.  Looking at row 2 in the table below we have 65 items.  We want to know how many full sets of 12 are in 65.  We will first use the FLOOR.MATH function to round down to the nearest multiple of 12.  We will use the formula =FLOOR.MATH(A2,B2) to round down the value 65 to the next multiple of 12 which gives us a result of 60.  Now we can take that value and divide it by the number of items in a complete set which is 12.  This gives us a result of 5 which is the number of complete sets in 65. The formulas entered in column C are displayed in column D.

Excel FLOOR.MATH computes complete sets

Using the Mode argument

Any nonzero value used for the mode argument will reverse the normal direction for the FLOOR.MATH function by making a negative number round up.

Excel FLOOR.MATH function used with Mode argument


Assignment using the FLOOR.MATH and MOD functions

See if you can solve the following problem using the spreadsheet below:

An egg case can hold 360 eggs. You need to determine the number of egg cases needed to hold the eggs and the number of eggs left over that will not fill a case. Use the FLOOR.MATH function to determine the Number of Cases Needed. Use the MOD function to determine the number of left over eggs. Complete the spreadsheet below.  If you need help solving it. click on Download answer to spreadsheet.