Floor Function

Today in this article I will discuss with you about Floor Function which is categorized as Math function  in Microsoft Excel.

Let us first understand what Floor function does !!!

In simple words it rounds down a number, your next question is what is the difference between ROUND and FLOOR function or what is the difference between ROUNDDOWN and FLOOR function.

The difference is FLOOR Function rounds a number down, towards zero, to the nearest multiple of 'Significance'.

It seems a little bit complicated but trust me after reading this article you will be able to understand and use the Floor function. Let us first understand the Syntax of this function.

Syntax

The syntax for the FLOOR function in Microsoft Excel is,

=FLOOR( number, significance )

Parameters or Arguments

number

The number that you wish to round down.

significance

The multiple of significance that you wish to round a number to.

Example:

We will use an example to understand FLOOR function.

Suppose, 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.

So if we tabulate this problem, it will be like below.

How to use Floor Function in Excel

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.

The formula will be : =FLOOR(C3,1000)

How to use Floor Function in Excel

The first part of the problem is done, now we will also calculate the second part of the said problem by simply multiplying it by 5%.

I think you have fully understood where and how to use floor function, in the above example we used FLOOR function in excel to deal with significant value and it is also useful in calculations for currency conversions, discounts.

How to use Floor Function in Excel

Using FLOOR function in excel, we can round the time values to the nearest time interval. The process is same, you need to put the time in significant parameter.

How to use Floor Function in Excel to calculate Time

Note :

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

v If the number is a negative number the FLOOR function will round the value away from the zero.

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

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

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

Hope you have enjoyed ...

Keep reading, sharing & Stay blessed ...