Google Sheets Floor Function is similar to another function in it called MROUND. But there is one difference. The former rounds down a number to the nearest integer multiple of a specified significance but the latter rounds to the nearest integer multiple. I’ll explain here how to use the Floor Function in Google Sheets.
Syntax:
FLOOR(value, [factor])
Learn Formula Arguments:
Value: The “value” is the number that the formula rounds down.
Factor: The given “value” will be rounded based on the “factor” provided.
Before going to the example on how to use the Floor function in Google Sheets, know something more about Factor. Factors are those numbers that we can use to multiply together to get another number. For example;
2 x 7 = 14
In this example, 2 and 7 are factors of the number 14. With the following examples, I’m trying to shed some light on the usage of Floor function in Google Sheets.
Example of the Use of Floor Function in Google Sheets
You can see that the above syntax arguments – Value in column A and Factor in Column B – in the following example.
Instead of using the cell references in the Floor formula in Column C, you can use the formulas as below. For example, the formula in Cell C2 can be like this.
=floor(8,3)
Result: 6
This Floor formula rounds down number 8 to the nearest integer multiple (not decimal) of factor 3.
It would be like;
3*1=3
3*2=6
3*3=9
So when the formula rounds down number 8 to the integer multiple of 3, the value would be 6.
If it’s rounded up, then the value would be 9. To do this, there is another function called CEILING.
Things to remember!
1. The “factor” must be positive but the “value” can be positive or negative.
2. The “factor” may not be equal to 0.
3. If you check the syntax you can see that the “factor” is optional. If you omit the “factor” it would be considered as 1.
4. The “factor” can be decimal place values.
Practical Use of Floor Function in Google Sheets
Unlike Google Sheets ROUND functions, Floor function in Google Sheets offers more control over the rounding of decimal places.
Example:
=floor(22.97,0.15)
Result: = 22.95
That means you can get great control over the rounding of decimal places in Google Sheets using Floor.
Floor function can be useful to round down to a particular denomination of currency after currency conversion.
Further, you can use Floor function to round down the time and that applicable to hours, minutes and seconds.
Hope you could understand how to use Floor function in Google Sheets. Enjoy!