The FLOOR function in Google Sheet rounds down any specific number to the nearest multiple of any given number. It works similar to the MROUND function but the difference between these two is MROUND can round up and down to the nearest multiple but FLOOR always rounds down to the nearest multiple.
- number : This will be rounded down by FLOOR function
- multiple: The number will be rounded down to the nearest multiple of this mentioned significance
Let’s understand in detail with a simple example as follows. Here we have the below table, In column A there are numbers which will be rounded down to the nearest multiple of the corresponding number in column B. In column C we have used the FLOOR function. In cell C1 the function we added as follows and dragged the same for the following cells in column C hence automatically updated the cell references of column A & B.
As you can see below each cell in column C calculates the respective number in column A rounded down to the nearest multiple of column B by FLOOR function.
We have used both positive and negative numbers and both decimal and integer numbers in all possible combinations to show every combination works with FLOOR function except for the case where the argument number is positive and multiple is negative(see row# 5 above). That will result error. In Excel if the number is negative and the multiple is positive then also it throws error but in Google sheet that is being handled(see row# 4 above).
In the above example we can also see in row# 3, the number in cell A3 is already an exact multiple of cell B3, means 15 is the exact multiple of 3, hence in cell C3 no rounding happened.
In our next example we will learn how we can use the FLOOR function to round down a time. In the below example in column B we have rounded down the times mentioned in cell A1 and A2 to the nearest multiple of 30 minutes.
Below is the formula we used in cell B1 and dragged the same for cell B2 to update the cell reference to A2.
With this formula, the time 7:29 has been rounded down to 7:00 which is the nearest multiple of 30 minutes. Same way in the next row, the time 12:06 has been rounded down to 12:00.