The FLOOR function in Google Sheets rounds a number down to the nearest integer multiple of a specified significance.
If the value is negative, and the factor is negative, this function rounds the value up towards zero. The FLOOR.MATH and FLOOR.PRECISE functions are more robust replacements for the FLOOR function.
Contents
Syntax
=FLOOR(value, factor)
value – The number for the function to round
factor – The multiple to round down to
Possible Errors
#NUM! – The value and the factor are different signs. Make both positive or negative.
#VALUE! – An argument is non-numeric.
Insert Math Symbols (Add-On)
Examples
You can use FLOOR in many different ways. Let’s take a look at a few, starting with rounding currency.
Example 1 – Round Down to the Next Nickel
Sometimes, presenting a price rounded down to the next nickel looks better. Let’s look at how to do that.
The formula used in cell C2: =FLOOR(A2,B2)
We use the FLOOR function to round the Original Price to a factor of $0.05. Since the function rounds down, it rounds $1.23 down to $1.20 in row 2 and rounds $1.27 down to $1.25 in row 4. Google Sheets does not round the value in row 3 because it is already a multiple of $0.05.
Example 2 – Round Down to the Nearest Half-Hour
Next, let’s round some time values. It is common to refer to a time as a rounded value. In this example, we’ll round values down to the nearest half-hour.
The formula used in cell C2: =FLOOR(A2,B2)
⚠️ You can enter a half hour as “0:30” or “0:30:00“. Either way, you’ll need to use custom formatting to remove the seconds, like in the example above in column B.
Rows 2 and 4 both get rounded down to the previous half-hour. As 1:30 PM in row 3 is already a multiple of 30 minutes, the FLOOR function does not change it.
Example 3 – Round With Different Factors
Up to this point, we have been changing the value. Now, let’s look at changing the factor. We’ll use positive and negative values and factors to see the differences.
You can see that negatives and positives move towards the same absolute values. Each output in column C is the opposite sign when the inputs flip their signs.
Live Example in Sheets
Make a copy of this spreadsheet to get the examples in your Google Sheet.