FLOOR Function in Excel

Below is the 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.

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: 

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.

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:

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.

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:

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.

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.

• 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.