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 number as 10 and the significance is 3 the output would be 9, this function takes two arguments as an input one is a number while other is the significance value.
Excel FLOOR Function (Table of Contents)
FLOOR Function in Excel
FLOOR 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, that is greater than the value of the number or less than the value of the number. Thus, a number, when rounded up has equal or greater value than the previous number which is rounded and similarly when a number is rounded down it has the equal or lower value than the number rounded down.
When a number is rounded towards the zero, they always become lesser in value, however, when a negative number is rounded towards zero, they get larger.
FLOOR function in excel always rounds the value down towards zero and always returns a numeric value. FLOOR in excel is in the list of the basic rounding functions in Excel, though it works in a similar manner like MROUND function, 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 and both are required. Significance means the factor that will help to find a value which is the nearest multiple the number.
Number: is the number that we want to round
Significance: is the multiple or factor to which 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 will use the FLOOR function.
How to Use FLOOR Function in Excel?
It is very simple and easy to use. Let understand the working of the FLOOR Function by some examples.
Suppose, we have a list of products with their selling prices, discount percentage, discounted price and we want the discounted prices to be rounded to the nearest multiple of the significance.
For the given product list we want to round the discounted prices to 5 cents of significance. So, in order to calculate the Rounded price, we will use the FLOOR function.
We will use the FLOOR in excel to round off the value and the FLOOR formula will be:
Applying the above FLOOR formula to other cells, we have
We have a list of a sales team with their monthly sales. Each sales representative is allotted with the incentive price for every 1000$ sales which is 5% of the relevant sales amount, now we need to calculate the incentive amount that will be paid to the representative as an incentive at the end of the month.
In order to find the relevant sales which have to be the nearest multiple of 1000, we will use the FLOOR function in excel with the nearest factor of 1000.
So, the FLOOR formula in excel will be:
Dragging the above FLOOR formula in excel down to other cells we have,
For incentive, we will calculate the 5% of the relevant sale which will be
Dragging and applying the above FLOOR formula in excel to other cells, we have the desired output as shown below
Thus, FLOOR function is used while dealing with significant value and is useful in calculations for currency conversions, discounts. With the help of the FLOOR in excel, we can round the time values to the nearest time interval.
For example, the time value floored to the nearest value of an hour
Things to Remember
- If the number to be rounded is a positive number, the FLOOR function will round the value towards zero, that is it will lower the value of the number as much possible to the nearest significant factor.
- If the number is a negative number the FLOOR function will round the value away from the zero.
- If the number is the exact multiple of the significant value, there will be no rounding of the number and 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 that is 0 and divisibility by 0 means error and the Excel FLOOR function also throws an error when either of the argument is not numeric.
- In the previous version of Excel (2003 and 2007), the number and the significant values should have the same sign, else the excel FLOOR function would return an error, however, this limitation of 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 has been a guide to FLOOR Function in Excel. Here we discuss the FLOOR Formula in excel and how to use it with excel example and downloadable excel templates. You may also look at these useful functions in excel –