Floor Function In Excel Formula Examples How To Use

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 excelMROUND Function In ExcelMROUND is the MATH & TRIGNOMETRY function in Excel, and it is used to round a number to the nearest multiple numbers. For example, =MROUND(50,7), will round the number 50 to 49.read more, 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.

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 commodityCommodityA commodity refers to a good convertible into another product or service of more value through trade and commerce activities. It serves as an input or raw material for the manufacturing and production units.read more 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:

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.

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.

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#DIV/0! Error When#DIV/0! is the division error in Excel which occurs every time a number is divided by zero. Simply put, we get this error when we divide any number by an empty or zero-value cell.read more 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.