A to Z of Excel Functions: The FLOOR Function

Welcome back to our regular A to Z of Excel Functions blog. Today we look at the FLOOR function.

Have you found the FLOOR in this function? This function returns number rounded down, towards zero, to the nearest multiple of significance:

For example, if you are an American (which, let’s be honest is all Microsoft really cares about) and you want to want to avoid using cents in your prices (that makes no cents) and your product is priced at $4.44, use the formula =FLOOR(4.44, 0.05) to round prices down to the nearest nickel.

The FLOOR function employs the following syntax to operate:

The FLOOR function has the following arguments:

• number: this is required and represents the value you wish to round
• significance: this is also required. This is the multiple used for rounding.

It should be further noted that:

• if either argument is nonnumeric, FLOOR returns the #VALUE! error value
• regardless of the sign of number, a value is rounded down when adjusted towards zero.  If number is an exact multiple of significance, no rounding occurs
• it is noted if number is positive, significance should be too
• if number is negative, positive significance will round number away from zero (that’s right a flaw in FLOOR!).

Please see my example below: 

We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every business day.

A full page of the function articles can be found here.