The FLOOR function — round down to the previous multiple, every time.
FLOOR(number, significance) rounds toward zero to the previous multiple of significance. The “never over-promise” function — perfect for age bins, complete-unit counts, and conservative budget quotes. Unlike MROUND, it has no sign restriction (modern Excel). For explicit control over how negative numbers round, use the modern FLOOR.MATH variant (§4).
| Input | MROUND(x, 5)nearest | CEILING(x, 5)always up | FLOOR(x, 5)always down |
|---|---|---|---|
| 12 | 10 | 15 | 10 |
| 13 | 15 | 15 | 10 |
| 17.5 | 20 | 20 | 15 |
| 22 | 20 | 25 | 20 |
| 47.5 | 50 | 50 | 45 |
| -17 | #NUM! | -15 | -20 |
How to use FLOOR
- Type
=FLOOR(in the cell where you want the rounded-down value. - Pass the source number, then the significance— the multiple to round down to.
=FLOOR(A2, 5)rounds down to the previous 5. - Close the parenthesis and press Enter. FLOOR never returns a value above the input (for positive numbers).
- For nearest-multiple (not always-down), use MROUND. For always-up, use CEILING.
Mental model. Stand on the number line at your input value. Walk toward zero until you land on the next multiple of significance. That landing spot is FLOOR's answer. For positive inputs, that's walking down; for negatives, that's walking up (toward zero) — unless you use FLOOR.MATH with mode=1 to walk farther from zero instead.
FLOOR syntax and arguments
FLOOR(number, significance) — both required.FLOOR.MATH(number, [significance], [mode]) — last two optional.
5, 10, 12 (dozens), 0.05, 0.25. In FLOOR.MATH this defaults to 1 if omitted.number. 0 (default) rounds away from zero (matches plain FLOOR). 1 rounds toward zero (less negative). Has no effect on positive numbers.| Input | Formula | Result | Why |
|---|---|---|---|
12.7 | =FLOOR(12.7, 5) | 10 | Previous multiple of 5 below 12.7 |
15 | =FLOOR(15, 5) | 15 | Already a multiple — no change |
23 | =FLOOR(23, 10) | 20 | The age-bin pattern: 20-29 all map to 20 |
25 | =FLOOR(25, 12) | 24 | Two complete dozens; 1 unit short of the third |
0.347 | =FLOOR(0.347, 0.05) | 0.30 | Decimal significance works the same way |
-12 | =FLOOR(-12, 5) | -15 | Away from zero (more negative) — see §4 for control |
FLOOR examples
Same function, four common shapes of rounding-down problem.
Example 1: age binning
Group ages into 10-year buckets. 7 → 0 (the 0-9 bin); 23 → 20 (the 20-29 bin); 65 → 60. Cleanest formula for histogram-style grouping.
Example 2: count complete dozens
Inventory comes in dozens. Count how many full dozens fit, with the loose remainder beside it. 25 units → 24 (2 dozens) + 1 loose.
Example 3: conservative budget quote
Round a detailed budget DOWN to a presentable round number. $1,247 → quote $1,200. Useful for under-promising to clients, or for revenue forecasts where you'd rather beat the number than miss it.
Example 4: FLOOR.MATH for negative control
Plain FLOOR for -17 with significance 5 returns -20 — “down” in the away-from-zero sense. If you want “down” in the toward-zero sense (less negative), use =FLOOR.MATH(A2, 5, 1) → -15. The 3rd argument mode is the switch.
FLOOR.MATH and the negative-number rule
FLOOR's behaviour on negatives is the mirror of CEILING's, and surprises just as often. FLOOR.MATH gives you the explicit knob.
For positive numbers, “down” is unambiguous — toward zero is also the smaller value. For negatives, the two diverge. =FLOOR(-17, 5) returns -20 — that's “away from zero”, which is “down” in numerical terms (more negative = smaller). But intuitively, users often mean “round closer to zero”, which would give -15.
=FLOOR(-17, 5) → -20 (away from zero — the smaller / more negative)FLOOR.MATH mode=0 (default)
=FLOOR.MATH(-17, 5, 0) → -20 (same as plain)FLOOR.MATH mode=1
=FLOOR.MATH(-17, 5, 1) → -15 (toward zero — “ceiling” in absolute terms)For positive numbers, the mode argument has no effect. FLOOR.MATH is otherwise a strict superset of FLOOR and can replace it in any formula. FLOOR.MATH was added in Excel 2013; if you need Excel 2010 or older support, stick with FLOOR.
FLOOR vs MROUND, CEILING & ROUNDDOWN
Four functions, different jobs. Pick by direction (up/down/nearest) and unit (multiple/decimal).
| Function | Direction | Unit | Typical use |
|---|---|---|---|
| FLOOR | Always down (toward zero) | Multiple | Age binning, complete-unit counts, conservative quotes |
| MROUND | Nearest (ties away from zero) | Multiple | Display rounding to clean multiples |
| CEILING | Always up (away from zero) | Multiple | Pricing, pack sizes, billable time |
| ROUNDDOWN | Always down (toward zero) | Decimal place | Round to 2 decimals, previous integer |
Rule of thumb: FLOOR when the target is a multiple of 5/12/0.05 etc., ROUNDDOWN when the target is a decimal place. They overlap for powers of 10 — =FLOOR(A, 10) and =ROUNDDOWN(A, -1) are equivalent.
FLOOR frequently asked questions
6.01What does FLOOR do in Excel?▸
FLOOR(number, significance) rounds the first argument TOWARD zero to the previous multiple of the second. =FLOOR(12, 5) returns 10 — the multiple of 5 at or below 12. =FLOOR(15, 5) returns 15 (already a multiple). For negatives, plain FLOOR rounds away from zero: =FLOOR(-12, 5) returns -15. Use it for binning (age 20-29 all land on 20), counting complete units (dozens, packs), or conservative budget quotes.
6.02What's the difference between FLOOR and FLOOR.MATH?▸
FLOOR (since Excel 97) has quirks on negatives. FLOOR.MATH (Excel 2013+) adds an optional 3rd argument mode for explicit negative-number control: mode=0 (default) rounds away from zero, mode=1 rounds toward zero. =FLOOR.MATH(-17, 5, 0) returns -20; =FLOOR.MATH(-17, 5, 1) returns -15. Use FLOOR.MATH whenever you need explicit control.
6.03How is FLOOR different from MROUND?▸
FLOOR always rounds DOWN; MROUND picks the NEAREST. =FLOOR(13, 5) returns 10. =MROUND(13, 5) returns 15 (13 is closer to 15). Use FLOOR for binning, complete-unit counting, and conservative estimates. Use MROUND when “closest” is what you want.
6.04How do I round down to the nearest 10, 100, or 0.25?▸
Pass the multiple as the second argument. =FLOOR(A1, 10) rounds down to nearest 10; =FLOOR(A1, 100) to nearest 100; =FLOOR(A1, 0.25) to the nearest quarter at or below. Works for any positive significance. For decimal-place rounding (not multiples), use ROUNDDOWN instead.
6.05Does FLOOR work in Google Sheets?▸
Yes — Google Sheets has FLOOR and FLOOR.MATH with identical syntax and behaviour to Excel. =FLOOR(A1, 5) works the same on both. Sheets also accepts FLOOR with a 3rd mode argument similar to FLOOR.MATH for portability.
6.06What's the difference between FLOOR and ROUNDDOWN?▸
Both round toward zero — but to different units. FLOOR rounds to a MULTIPLE: =FLOOR(12.7, 5) returns 10. ROUNDDOWN rounds to a DECIMAL place: =ROUNDDOWN(12.7, 0) returns 12. =ROUNDDOWN(12.7, -1) returns 10 (previous multiple of 10). For nearest 5/15/0.05 use FLOOR; for nearest 1/10/100/0.01 either works. FLOOR's syntax is more direct when the unit isn't a power of 10.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.