FLOOR Function in Excel

Last Updated :

21 Aug, 2024

Blog Author :

Wallstreetmojo Team

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya

Table Of Contents

arrow

The FLOOR function in Excel is very similar to the ROUNDDOWN function as it rounds down the number to its significance.

For example, if we have a number as 10 and the significance is 3, the output would be 9. So, this function takes two arguments as an input one is a number while the other is the significance value.

FLOOR Function in Excel

The FLOOR function in Excel is a Math/ Trig function that rounds a number (towards zero) to the nearest specified multiple of significance.

Whenever a number is rounded, it is rounded either up or down, which is greater than the value of the number or less than the value of the number. Thus, when rounded up, a number has an equal or greater value than the previous number, which is rounded. Similarly, when a number is rounded down, it has a comparable or lower value than the number rounded down.

When a number is rounded toward zero, they always become lesser in value. However, it may get larger when a negative number is rounded towards zero.

The FLOOR function in Excel always rounds the value down towards zero and always returns a numeric value. The FLOOR function in Excel is in the list of the basic rounding functions in Excel. Though it works similarly to the MROUND function in excel, the only difference is that it always pushes down the number to the nearest multiple of the significance.

FLOOR Formula in Excel

Below is the Formula in Excel.

FLOOR Formula in Excel

This formula in Excel always takes two arguments: the number and the significance. Both are required. The significance means the factor that will help find a value, the nearest multiple of the number.

Number: It is the number that we want to round.

Significance: It is the multiple or factor we want to round the number.

FLOOR Function 1
FLOOR Function 2

If the price of a given commodity is $6.42, and we want to round it down to the nearest value divisible by 5 cents, we can use the FLOOR function.

=FLOOR(6.42,0.05)

Output: 

FLOOR Function 3

How to Use the FLOOR Function in Excel?

It is very simple and easy to use. Let us understand the working of the FLOOR function with some examples.

Example #1

Suppose we have a list of products with their selling prices, discount percentage, and discounted price. And, we want the discounted prices to be rounded to the nearest multiple of the significance.

FLOOR Function Example 1

We want to round the discounted prices to 5 cents of significance for the given product list. So, to calculate the rounded price, we will use the FLOOR function.

We can use the FLOOR function in Excel to round off the value. The FLOOR formula will be:

=FLOOR(E3,F3)

Applying the above FLOOR formula to other cells, we get the following result:

FLOOR Function Example 1-1

Example #2

We have a list of a sales team with their monthly sales. Each sales representative is allotted the incentive price for every $1,000 sale, 5% of the relevant sales amount. Next, we need to calculate the incentive amount paid to the representative as an incentive at the end of the month.

Example 2

To find the relevant sales, which have to be the nearest multiple of 1,000, we may use the FLOOR function in Excel with the most immediate factor of 1,000.

So, the FLOOR formula in Excel will be:

=FLOOR(B2,1000)

Dragging the above FLOOR formula in Excel down to other cells, we have the following outcome:

Example 2-1
Example 2-2

For incentive, we will calculate the 5% of the relevant sale, which will be:

=D3*(5/100)

We have the desired output by dragging and applying the FLOOR formula in Excel to other cells.

Example 2-3

Thus, the FLOOR function deals with significant value and is useful in calculating currency conversions and discounts. For example, with the help of the FLOOR function in Excel, we can round the time values to the nearest time interval.

Example #3

The time value floored to the nearest value of an hour.

Example 3

Things to Remember

  • Suppose the number to be rounded is positive. In that case, the FLOOR function will round the value towards zero, i.e., it will lower the value of the number as much as possible to the nearest significant factor.
  • If the number is negative, the FLOOR function will round the value away from zero.
  • If the number is the exact multiple of the significant value, there will be no rounding of the number. The FLOOR function will return the same value.
  • This function throws #NUM! Error when the number is positive and significance is a negative value. It throws #DIV/0! Error when the significant value is 0 because the function iterates the value by dividing the multiple until it gets the lowest value which is 0. Divisibility by 0 means error. The Excel FLOOR function also throws an error when either of the arguments is not numeric.
  • In the previous version of Excel (2003 and 2007), the number and the significant values must have the same sign. Else, the Excel FLOOR function would return an error. However, this limitation of the FLOOR function has been improvised in the latest version of Excel (2010 and later). Now it can round off a negative number with a positive significance.

This article is a guide to the FLOOR Function in Excel. Here, we discuss the FLOOR formula in Excel and use it with Excel examples and downloadable Excel templates. You may also look at these useful functions in Excel: -