Rounding Numbers in Excel
The FLOOR function is one of several functions in Excel that can be used for rounding numbers.
Most of these functions have very specific jobs so which one you use depends on the results you want.
In the case of the FLOOR function it can be used to round data downwards to the nearest multiple of a specified value.
What this means is that the function can be used to get rid of extra digits in a number by rounding that number down to the nearest value that is considered significant.
For example, the function can be used to round down the cost 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 ).
Changing Data with the Rounding Functions
Like other rounding functions, the FLOOR function actual alters the data in your worksheet and will, therefore affect the results of any calculations that use the rounded values.
There are, on the other hand, formatting options in Excel that allow you change the number of decimal places displayed by your data without changing the numbers themselves.
Making formatting changes to data has no effect on calculations.
The FLOOR Function's Syntax and Arguments
The syntax for the FLOOR function is:
= FLOOR ( Number, Significance )
Number - the value to be rounded. This argument can contain the actual data for rounding or it can be a cell reference to the location of the data in the worksheet.
Significance - indicates the number of significant digits after the decimal point that will be present in the answer. The Number argument specified above is also rounded down to the nearest multiple of this value.
Note: Both arguments (Number and Significance) must have the same sign - either positive or negative. If the sign for these two arguments is different, the Floor function will return the #NUM! error.
Excel's Rules for Rounding
In the rules for rounding numbers that Excel follows, normally the number to the right of the rounding digit determines whether the rounding digit will be rounded up or down:
- If the value of the number to the right of the rounding digit is less than five, the rounding digit is left unchanged
- If the value of the number to the right of the rounding digit is five or higher, the rounding digit is raised by one
The FLOOR function however, always rounds the rounding digit down to the level of significance specified in the function no matter the value of the number to the right of the rounding digit.
Example: Rounding Numbers with Excel's FLOOR Function
For help with this example, see the image above.
In this example we will use the FLOOR function to reduce the number of decimal places of a value from four to two.
Entering the Data
- Enter the number 34.2397 into cell D1
Entering the FLOOR Function
- Click on cell E1 to make it the active cell - this is where the results of the FLOOR function will be displayed
- 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 worksheet to enter that cell reference into the dialog box
- In the dialog box, click on the Significance line
- Type in 0.01 - the number in D1 will be rounded down to the nearest multiple of 0.01
- Click OK.
- The answer 34.23 should appear in cell E1
- How Excel arrives at this answer is that:
- first it removes the two insignificant digits (97) from the end of the number 34.2397
- since the remaining number - 34.23 - is the nearest multiple of 0.01 ( 0.01 * 3 ) no further changes are necessary
- When you click on cell E1 the complete function = FLOOR ( D1 , 0.01 ) appears in the formula bar above the worksheet