The FLOOR function is used to round a number downwards to the nearest multiple of a specified value.
For example, the function can be used to round down the costs of items to the nearest dime ( $ 0.10 ) to avoid having to deal with smaller change such as pennies ($ 0.01 ) and nickels ($ 0.05 ).
The syntax for the FLOOR function is:
= FLOOR ( Number, Significance )
Number - the value to be rounded.
Significance - the function rounds the Number specified above down to the nearest multiple of this value.
Example: Using Excel's FLOOR Function
- Enter the following data into cells D1 to D3: 34.57, 23.82, 31.16
- Click on cell E1 in the spreadsheet - this is where the function will be located.
- Click on the Formulas tab of the ribbon menu.
- Choose Math & Trig from the ribbon to open the function drop down list.
- Click on FLOOR in the list to bring up the function's dialog box.
- In the dialog box, click on the Number line.
- Click on cell D1 in the spreadsheet.
- In the dialog box, click on the Significance line.
- Type in 0.10 - the number in D1 will be rounded down to the nearest multiple of 0.10.
- Click OK.
- The answer 34.50 should appear in cell E1 since 34.50 is the nearest multiple of 0.10
( 0.10 * 5 ) lower than 34.57.
- Drag the fill handle in the bottom right corner of cell E1 down to cells D2 and D3 to copy the function to those cells.
- The cells D2 and D3 should have the answers 23.80 and 31.10 respectively.
- When you click on cell E1 the complete function = FLOOR ( D1 , 0.1 ) appears in the formula bar above the worksheet.