Excel Functions Tutorials
- Excel Formulas Cheatsheet
- Financial Functions in Excel
- Logical Functions in Excel
- TEXT Functions in Excel
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- Hyperlink Excel Function
- INDIRECT Function in Excel
- LOOKUP Excel Function
- Match Excel Function
- VLOOKUP Excel Function
- INDEX Excel Function
- VLOOKUP vs HLOOKUP
- Maths Functions in Excel
- POWER Function in Excel
- EVEN Function in Excel
- ODD Function in Excel
- ABS Function in Excel
- SUM Function in Excel
- SUMPRODUCT Function in Excel
- SUBTOTAL Excel Function
- ROUND in Excel
- AGGREGATE Excel Function
- PRODUCT Excel Function
- RAND Excel Function
- LOG Excel Function
- EXPONENTIAL Excel Function
- SUMIF in Excel
- TAN Excel Function
- CEILING Excel Function
- LN Excel Function
- SIGN Excel Function
- COS Excel Function
- FLOOR Function in Excel
- SIN Excel Function
- Date and Time Function in Excel
- Statistical Function in Excel
FLOOR in Excel (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 FLOOR in excel works in a similar manner like MROUND function, the only difference is that FLOOR in excel always pushes down the number to the nearest multiple of the significance.
FLOOR Formula in Excel
Below is the Floor Formula in Excel.
FLOOR 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 a 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 in excel
How to Use FLOOR Function in Excel?
FLOOR function is very simple and easy to use. Let understand the working of FLOOR in excel by some examples.
FLOOR in Excel Example #1
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 in excel.
We will use the FLOOR in excel to round off the value and the FLOOR formula in excel will be:
Applying the above FLOOR formula in excel to other cells, we have
FLOOR in Excel Example #2
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 a 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 in excel is used while dealing with significant value and is useful in calculations for currency conversions, discounts. With the help of FLOOR in excel, we can round the time values to the nearest time interval.
FLOOR in Excel Example #3
For example, the time value floored to the nearest value of an hour
Things to Remember About FLOOR Function in Excel
- 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.
- The FLOOR function in Excel 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.
You can download this FLOOR Function in Excel template here – FLOOR Function Excel Template
This has been a guide to FLOOR Function in Excel. Here we discuss the FLOOR Formula in excel and how to use FLOOR function in excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –