The FLOOR.MATH function in Google Sheets rounds a number down to the nearest integer multiple of a specified significance. You can round negative numbers toward or away from zero depending on the setting.
ℹ️ FLOOR.MATH and FLOOR.PRECISE are newer versions of the FLOOR function.
Syntax
=FLOOR.MATH(number, [significance], [mode])
number – The number for the function to round
significance – Optional. The multiple to round down to. The default value is 1.
mode – Optional. The rounding direction for negative numbers. This has no impact on positive numbers.
0– Round away from zero1– Round toward zero
Similar Functions
Several functions deal with rounding. Choose the most appropriate for your use.
- CEILING.MATH – Rounds a number up to the nearest integer multiple of specified significance with custom negative number treatment
- CEILING.PRECISE – Rounds a number up to the nearest integer multiple of specified significance
- INT – Rounds a number down to the nearest integer
- FLOOR.MATH – Rounds a number down to the nearest integer multiple of specified significance with custom negative number treatment
- FLOOR.PRECISE – Rounds a number down to the nearest integer multiple of specified significance
- MROUND – Rounds a number to the nearest multiple of another number
- ROUND – Rounds a number to a specified number of decimal places using standard rounding
- ROUNDDOWN – Round a number down to a specified number of places
- ROUNDUP – Round a number up to a specified number of places
- TRUNC: Truncates a number to a certain number of significant digits by omitting less significant digits
Possible Errors
#VALUE! – An argument is non-numeric.
Insert Math Symbols (Add-On)
Examples
You can use FLOOR.MATH 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 more attractive to a customer. Let’s look at how to do that.
The formula used in cell C2: =FLOOR.MATH(A2,B2)
We use the FLOOR.MATH function to round the Original Price to a significance of $0.05. Since the function rounds down, it rounds $1.23 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 closest half-hour.
The formula used in cell C2: =FLOOR.MATH(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 last half hour. As 1:30 PM is already a multiple of 30 minutes, the FLOOR.MATH function does not change the time in row 3.
Example 3 – Round With Different Significances
Up to this point, we have been changing the number. Now, let’s look at changing the significance. We’ll use positive and negative significances to see the differences.
You can see that positives move toward zero while the negatives move away from zero. If you want negative values to move toward zero, you must change the mode argument’s value. Let’s look at that in the following example.
Example 4 – Rounding Negative Numbers with Different Modes
In previous examples, we did not specify a value for the mode argument. Now, we will use it to tell the function in which direction to round negative values.
In rows 2 through 4, the mode of 0 makes the function round negative values away from zero. Specifying a mode of 1 (or anything other than 0) moves the values toward zero in rows 5 through 7.
Live Example in Sheets
Make a copy of this spreadsheet to get the examples in your Google Sheet.