Excel FLOOR(): How to Round Down Numbers in Excel

If you're someone who spends time managing budgets or if you work with prices in some other way, you'll find Excel's FLOOR() function helpful.

In this article, I’ll walk through exactly how FLOOR() works, see it in action with real examples, and explore how it can bring consistency and accuracy to your calculations.

How Excel FLOOR() Works

Before you can put FLOOR() to work, it’s important to understand its underlying logic, and also how you can control its behavior using its arguments.

FLOOR() is all about rounding numbers down. You can round to the nearest whole number and you can also specify a “significance,” or increment, so you can round down based on steps that make sense for your scenario. This is especially handy for things like unit prices or fixed time intervals, where rounding needs to follow some kind of pattern.

This is how you call FLOOR() in Excel:

=FLOOR(number, significance)

• number: The value you want to round down.

• significance: The multiple to which you want to round.

Both arguments are required for FLOOR() to work properly.

The function always rounds down toward zero, using the significance as the step size.

How to round down a positive number

To practice, let’s try a basic example. Suppose you want to round the number 17 down to the nearest 5:

=FLOOR(17, 5)

This formula returns 15.

How to round down a negative number

If, on on the other hand, your number is negative, FLOOR() still rounds toward zero, so:

=FLOOR(-17, 5)

This returns -20.

Practical Uses for Excel FLOOR()

You might now be wondering where to apply FLOOR() in real-world situations. Here are some common and useful ways:

Controlling prices or costs

One classic use of FLOOR() is in pricing, where you want to round down to fixed increments to avoid accidental overcharges. For instance, if your products are priced in steps of 25 cents, you can ensure all prices land on those increments:

=FLOOR(2.89, 0.25)

This formula returns 2.75, neatly rounding 2.89 down to the next lowest quarter-dollar.

Organizing time in fixed blocks

FLOOR() is also great for managing time, especially when you need to group durations into consistent intervals. Imagine you want to fit any number of minutes into 15-minute blocks:

=FLOOR(52, 15)

This returns 45, so 52 minutes gets slotted into the 45-minute block.

FLOOR() vs. ROUNDDOWN() and MROUND()

Excel does offer several rounding functions, each with its own style and purpose. In this section, we’ll take a closer look at how FLOOR() compares to ROUNDDOWN() and MROUND().

Comparing rounding methods

Here’s how the main rounding functions differ:

• FLOOR(): Rounds down to the nearest multiple you specify.

• ROUNDDOWN(): Rounds toward zero, but you control the number of decimal places, not the multiple.

• MROUND(): Rounds to the nearest multiple, either up or down, depending on which is closer.

For example, with:

=ROUNDDOWN(17.7, 0)

You get 17 - just the integer portion - since ROUNDDOWN() works based on decimal places.

But if you use:

=MROUND(17, 5)

You get 15, because 15 is the nearest multiple of 5 (either up or down).

FLOOR(), in contrast, always rounds down. (Actually, =FLOOR(17.7, 0) would give us a divide #DIV/0! error.)

Common Issues and Behavior

While FLOOR() is straightforward in most cases, there are a few quirks to watch out for. In this section, we’ll explore the most common errors and how to avoid them.

Significance and number sign

A key rule with FLOOR() is that both the number and the significance must have the same sign. If you mix a positive number with a negative significance (or vice versa), Excel will return an error. For example:

=FLOOR(10, -2)

This produces a #NUM! error.

If you think about it - a positive number with a negative significance would imply rounding down toward zero, which contradicts the purpose of FLOOR(). And a negative number with a positive significance would imply rounding up toward zero, which is the same story.

To avoid problems, always make sure both inputs are either positive or negative.

Zero significance

Another common issue arises if you try to use zero as the significance:

=FLOOR(10, 0)

Again, Excel returns a #NUM! error, since it can’t round to a zero increment. I started to call this out earlier when comparing FLOOR() to similar functions. To avoid this, just make sure you never put zero in the second argument.

Conclusion

Now you know all about the FLOOR() function in Excel. I think it's helpful in financial modeling because rounding down ensures that projections or costs do not overstate potential revenue or returns. It’s a cautious approach that can serve you well in risk-averse contexts. We have a popular Financial Modeling in Excel course that is definitely worth taking, if you are interested in this kind of thing.