In this article, we’ll talk about the Floor Function in Excel. A Math/Trig Function in Excel that rounds a value (towards zero) to the chosen double of significance is known as FLOOR
Even if a number is rounded, it is rounded over or under, depending on whether the number appears to be higher or less than the number’s value. When a number is rounded up, it has the same or larger value than the previous rounded number, and when a number is rounded down, it has the same or lower value as the previous rounded number.
When a positive figure is dropped to zero, it loses value; yet, when a negative number is rounded to zero, it gains value.
The floor function in Excel is equivalent to the round-down feature in that it rounds down on the number to its significance value. For example, if the number is 10 and the significance is 3, the output will be 9. This function takes two arguments as input, one of which is a number and another is the highly significant value.
Also read: Mathematical Functions in Excel – Part 1
FLOOR Formula in Excel
=FLOOR(number, significance)
This equation in Excel always requires two arguments, the number, and the significance, both of which must be present. The term “significance” refers to the component that will assist in determining a value, which is the number’s nearest multiple.
- The figure we want to round is called the “number”.
- The multiplicity or ratio toward which we desire to round the number is called “significance”.
The FLOOR method is used if the price of a product is $7.42 and we want to round it down to the nearest number divisible by 5 cents.
The answer will be {FLOOR = 7.42.0.05}
Now, let’s see how we can use this FLOOR method by taking into consideration various examples.
Example 1 for FLOOR() in Excel
Just entire products, i.e. products with only 5 quantities, must be sorted out in this FLOOR function in Excel example. The values are listed in column B; now, using the floor function in column C, I’d want to group them into multiples of five. In Excel, you must perform the following steps:
Choose the output cell where we want to determine the floor value, in this case C8.
A dialogue box will display when you click the insert function button (fx) under the formula toolbar. In the search for just a function box, type “floor”; the FLOOR method will appear in the choose a function box. FLOOR is activated by a double-click.
A dialogue window displays, prompting you to fill in or input arguments for the floor function (number and significance).
Amount one is 14; the significance value is 5, and the Significance value is the same for all cells.
The FLOOR formula is used in the C8 cell, which is =FLOOR (B8,5).
The result will be that the number 14 will be rounded to 10.
It’s then performed on the rest of the cells in that column to achieve the desired result.
The above picture shows the final result. Using the FLOOR Method in excel is simple, you just have to take care while writing the amounts. If you had written the wrong amount of something then your whole excel sheet will be affected.
Example 2 for FLOOR() in Excel
The numerical variable is a negative value (-4.5) in cell B22, and the significance value is likewise a negative value (-2) in cell C22 in the second example of the FLOOR function in Excel. For the number -4.5, I need to determine the nearest multiple of -2.
Choose the output cell where we want to determine the floor value, in this case, D22.
Insert the Floor function as discussed in the previous example.
The first value is -4.5, and the significance value is -2, and both are negative numbers. In a D22 cell, the FLOOR formula is used, i.e. =FLOOR (B22, C22).
The ultimate result will be: -4.5 will be rounded down to the next number of -2, and the number -4 will be returned.
By using the FLOOR method you can also find the final results of every amount. Also, various sections like we had seen the result of B22 whose value was -4.5 and the significance value was -2 so we got the final result of -4.
Conclusion
That’s it for the FLOOR function in Excel. It’s an easy way to get your numbers rounded down to a figure that is human-friendly and can be used for various approximations better than directly using the data.