Excel’s FLOOR function is a different way to round numbers in Excel and instead of specifying a digit or decimal point, you specify a multiple. The multiple specified in the  FLOOR formula in Excel is useful from rounding downtime for employees’ work hours to setting price after discounts, and even FX rate conversions.

By the end of this article, you will have a good grasp of what the FLOOR function in Excel is and how to use it.

Laying the Ground Work for Excel’s FLOOR Function

The FLOOR function in Excel is only one of the many ways to Round numbers under Math/Trigonometry Formulas. Excel offers a wide array of options to round numbers, from a function that follows conventional Math rounding rules to functions that do not and allows users to specify to round up or down. Instead of using decimal places and rounding digits, the Excel FLOOR function rounds down numbers to a specific multiple.

Recall in Basic Math class, a multiple is a result of multiplying a number by a non-zero whole number. Here are basic examples of Multiples:

The FLOOR Formula in Excel:

Where:

Number – A required argument representing the number that has to be rounded. The number argument can be positive and negative numbers, cell references, and formulas.

Significance – A required argument representing the multiple to round a number to e.g. multiple of 2, 3, etc. The multiple can have both positive and negative numbers and refer to cells.

Here are some basic applications of the FLOOR formula in Excel using the Multiples table we presented previously.

• When both arguments have POSITIVE ( + ) signs, the FLOOR formula in Excel rounds the number DOWN to the nearest multiple (see cells D2 and D3). Notice how the results are rounded down towards zero.
• When both arguments have NEGATIVE ( – ) signs, the FLOOR formula in Excel rounds the number DOWN to the nearest multiple (see cells D4 and D5). Notice how the results are still rounded down towards zero.
• If the Number argument is NEGATIVE and Significance argument is POSITIVE, the FLOOR formula in Excel rounds the number DOWN to the nearest multiple but notice the result is rounded down away from zero (see cell D6).
• If the Number argument is POSITIVE and Significance argument is NEGATIVE the FLOOR formula in Excel results in a #NUM error (see cell D7).

How to Use the FLOOR Function in Excel

Assume a start-up company just opened up for business and to attract customers, management decided to offer a discounted introductory price to consumers.

To make the price more enticing, management decided to round the discounted price to the nearest $2 or multiple of 2.

Using the FLOOR formula in Excel =FLOOR(17.655,2) for Gadget A1, the formula rounds down the price to the nearest multiple of 2 or $2. The new discounted price is $16.

The FLOOR formula in Excel can also be combined with other Excel functions like IF statement, AND/OR logic, etc. to create a more dynamic formula.

Assume management does not want its price strategy to adversely affect revenue. So they decided to only offer such prices if the discounted price is no less than $20 otherwise not to offer the price (zero discounted price). How to use Excel’s FLOOR function for this scenario? By combining the FLOOR formula in Excel with IF Statement.

The FLOOR formula in Excel with IF Statement is =FLOOR(IF(D2>20,D2,0),2). Let’ go over the combined formula:

1. The Number argument is the IF statement- IF(D2>20,D2,0). The IF function is used to incorporate the criteria set by management; that is the discounted price must be no less than $20 otherwise not to offer.
2. Gadget A1’s discounted price of $17.655 is less than $20, this does not meet the criteria set by management. So this will not be offered and thus price is zero.
3. The Significance argument =FLOOR(….),2) is the multiple 2 or rounded to the nearest $2.
4. Since the result of the Number argument is zero and the Significance argument has nothing to round down, the result is zero.

Remember The Foundations Of Excel’s FLOOR function

• Excel’s FLOOR function rounds POSITIVE numbers down towards zero while it rounds NEGATIVE numbers down away from zero.
• Below are common errors encountered
  • The #VALUE! Error occurs when one of the arguments CEILING formula in Excel is non-numeric.
  • The #NUM Error occurs when:
    • If you’re using MS Excel 2010/2013 versions, when the Number argument is positive and the Significance argument is negative, the CEILING formula in Excel returns #NUM error.
    • If you’re using MS Excel 2003/2007 versions, the Significance argument must have the same arithmetic sign (positive or negative) as the Number argument.

• Both FLOOR function in Excel and ROUNDDOWN function rounds numbers DOWN. But the key difference is FLOOR function in Excel rounds to the nearest significant multiple while the latter rounds to the nearest digits or decimal places.
• If you want to round numbers up with multiples, use the opposite of the  FLOOR function in Excel and use CEILING function instead.