What's the difference between FLOOR.MATH and FLOOR?

FLOOR is older and requires both arguments explicitly; FLOOR.MATH defaults significance to 1 and offers a mode parameter for clearer negative-number handling. FLOOR.MATH also provides better control over whether negatives round toward or away from zero. Use FLOOR.MATH for new sheets; FLOOR is mainly for legacy compatibility.

Why does FLOOR.MATH return 0 when I expected a different result?

If your significance is larger than your number, the result rounds down to zero or the nearest multiple below your value. For example, FLOOR.MATH(250, 1000) returns 0 because 0 is the largest multiple of 1000 that doesn't exceed 250. Verify your significance value matches your intended rounding scale.

When should I use the mode parameter?

Use mode=1 if you're rounding negative numbers and want them to always go down (toward negative infinity). The default mode=0 rounds toward zero, so -31.375 becomes -31 instead of -32. For salary and positive-only data, mode doesn't matter; it's mainly for financial calculations or data with negative values.

Can I use FLOOR.MATH to round to decimal places like 0.01?

Yes—set significance to 0.01 for cents, 0.001 for thousandths, or any decimal increment. For example, FLOOR.MATH(42.756, 0.01) returns 42.75. This works reliably in Excel and Google Sheets, making FLOOR.MATH a good alternative when you need downward-only rounding at any decimal precision.

FLOOR.MATH function

Rounds a number down to the nearest multiple of a specified significance value, commonly used for budget categories and interval-based rounding.

=FLOOR.MATH(number, [significance], [mode])

Generate a FLOOR.MATH formula

Describe what you need. The generator will reach for FLOOR.MATH where FLOOR.MATH is the right tool, and tell you when it is not.

Arguments

How FLOOR.MATH reads its arguments

Argument	Required	Description
number	Required	The value to round down; can be positive, negative, or zero.
significance	Optional	The multiple to round to (default 1); must be non-zero and positive when mode=0.
mode	Optional	Rounding direction for negatives: 0 (default) rounds toward zero; 1 rounds toward negative infinity.

Returns

A number representing the input rounded down to the nearest multiple of significance.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Round salaries to nearest $5000 for compensation bands

Employee ID	Name	Salary
1001	Sarah Chen	85500
1002	Marcus Lopez	62750
1003	Emma Wilson	58200

=FLOOR.MATH(C2, 5000)

Result: Sarah: 85000; Marcus: 60000; Emma: 55000

FLOOR.MATH rounds each salary down to the nearest $5000 interval for budget classification. Sarah's $85,500 becomes $85,000, Marcus's $62,750 becomes $60,000, and Emma's $58,200 becomes $55,000—all useful for grouping employees into compensation bands without rounding up.

2. Round calculated bonuses to nearest $100

Employee ID	Name	Salary	Bonus (5%)
1001	Sarah Chen	85500	4275
1002	Marcus Lopez	62750	3137.5
1005	Priya Patel	71400	3570

=FLOOR.MATH(D2, 100)

Result: Sarah: 4200; Marcus: 3100; Priya: 3500

Bonuses calculated as 5% of salary are rounded down to the nearest $100 for payroll simplicity. Sarah's $4,275 bonus becomes $4,200; Marcus's $3,137.50 becomes $3,100. This ensures clean bonus amounts and avoids fractional cents in payroll processing.

3. Round hourly rates down to nearest quarter

Employee ID	Name	Annual Salary	Hourly Rate (÷2000)
1001	Sarah Chen	85500	42.75
1002	Marcus Lopez	62750	31.375
1004	James Kumar	95300	47.65

=FLOOR.MATH(D2, 0.25)

Result: Sarah: 42.75; Marcus: 31.25; James: 47.50

Hourly rates are rounded down to the nearest 25¢ for consistent billing and time-tracking systems. Marcus's calculated $31.375/hour rounds down to $31.25, while James's $47.65 becomes $47.50. This keeps billing rates in predictable increments that align with quarter-dollar accounting.

Common errors

Which FLOOR.MATH error are you seeing?

Error	Why it happens	How to fix it
#NUM!	The significance argument is 0 or negative, which is invalid because FLOOR.MATH cannot round to an invalid multiple.	Use a positive, non-zero significance value; e.g., =FLOOR.MATH(85500, 1000) instead of =FLOOR.MATH(85500, 0).
#VALUE!	The number argument is text, a date, or another non-numeric type that FLOOR.MATH cannot process.	Ensure the number argument is numeric; if pulling from a text column, use VALUE() to convert: =FLOOR.MATH(VALUE(A2), 1000).
#VALUE!	The significance argument is text or non-numeric, preventing FLOOR.MATH from identifying the rounding interval.	Verify significance is numeric; e.g., =FLOOR.MATH(85500, 1000) not =FLOOR.MATH(85500, "1000").

Tips and when to use something else

Use significance=1000 to round to nearest thousand, or 0.01 to round to nearest cent—the interval adapts to your scale.
Set mode=1 when working with negative numbers if you want them to round down (away from zero); the default mode=0 rounds toward zero, which may surprise you with negatives.
For quick rounding to powers of 10 (10, 100, 1000), FLOOR.MATH is cleaner than nested INT or TRUNC formulas.
If you need to round UP instead of down, use CEILING.MATH; FLOOR.MATH always goes down, so choose the right function for your business logic.

Related functions

CEILING.MATHCEILING.MATH rounds numbers up to the nearest multiple of a given significance value, useful for conservative cost estimates and price rounding.ROUNDROUND returns a number rounded to a specified number of decimal places, using standard rounding rules (0.5 rounds up).ROUNDDOWNROUNDDOWN returns a number rounded down toward zero to the specified number of decimal places, useful for inventory and billing calculations.INTINT rounds any number down to the nearest whole integer, always discarding decimal places and rounding toward negative infinity.MROUNDMROUND rounds a number to the nearest multiple of a specified value, useful for rounding to standard increments like 5, 10, or 100.

Frequently asked questions

What's the difference between FLOOR.MATH and FLOOR?: FLOOR is older and requires both arguments explicitly; FLOOR.MATH defaults significance to 1 and offers a mode parameter for clearer negative-number handling. FLOOR.MATH also provides better control over whether negatives round toward or away from zero. Use FLOOR.MATH for new sheets; FLOOR is mainly for legacy compatibility.
Why does FLOOR.MATH return 0 when I expected a different result?: If your significance is larger than your number, the result rounds down to zero or the nearest multiple below your value. For example, FLOOR.MATH(250, 1000) returns 0 because 0 is the largest multiple of 1000 that doesn't exceed 250. Verify your significance value matches your intended rounding scale.
When should I use the mode parameter?: Use mode=1 if you're rounding negative numbers and want them to always go down (toward negative infinity). The default mode=0 rounds toward zero, so -31.375 becomes -31 instead of -32. For salary and positive-only data, mode doesn't matter; it's mainly for financial calculations or data with negative values.
Can I use FLOOR.MATH to round to decimal places like 0.01?: Yes—set significance to 0.01 for cents, 0.001 for thousandths, or any decimal increment. For example, FLOOR.MATH(42.756, 0.01) returns 42.75. This works reliably in Excel and Google Sheets, making FLOOR.MATH a good alternative when you need downward-only rounding at any decimal precision.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17