How to use the Excel FLOOR function

The Excel FLOOR perform rounds a quantity right down to a given a number of. The a number of to make use of for rounding is offered because the significance argument. If the quantity is already a precise a number of, no rounding happens and the unique quantity is returned.

The FLOOR perform takes two arguments, quantity and significance. Quantity is the numeric worth to spherical down. The significance argument is the a number of to which quantity needs to be rounded. Typically, significance is offered as a numeric worth, however FLOOR also can perceive time entered as textual content like “0:15”. See the instance beneath.

FLOOR works just like the MROUND perform, however not like MROUND, which rounds to the nearest a number of, FLOOR at all times rounds down.

Observe: the FLOOR perform is formally listed as a compatibility perform, changed by FLOOR.MATH and FLOOR.PRECISE.

Examples

The formulation beneath present how FLOOR rounds down values to a given a number of:

=FLOOR(10,3) // returns 9
=FLOOR(40,7) // returns 35
=FLOOR(320,25) // returns 300
=FLOOR(610,100) // returns 600
=FLOOR(-5.4,1) // returns -6

To spherical a quantity in A1 down to the closest a number of of 5, you should use a formulation like this:

Spherical right down to nearest 5
To spherical a quantity in A1 right down to the closest a number of of 5:
=FLOOR(A1,5) // spherical right down to nearest 5

Spherical pricing down to finish with .99
FLOOR can be utilized to set pricing after forex conversion, reductions, and so on. For instance, the formulation beneath will spherical a quantity in A1 right down to the following entire $, then subtract 1 cent, to return a price like $2.99, $5.99, $49.99, and so on.

You possibly can spherical pricing up to finish in .99 with an identical formulation primarily based on the CEILING perform:

Spherical time right down to nearest quarter-hour
FLOOR understands time codecs, and can be utilized to spherical time right down to a given a number of. For instance, to spherical a time in A1 right down to the earlier 15 minute unit, you should use FLOOR like this:
=FLOOR(A1,"0:15") // spherical time right down to nearest 15 min

Different rounding capabilities in Excel
Notes

FLOOR works just like the MROUND perform, however FLOOR at all times rounds down.
If a quantity is already a precise a number of of significance, no rounding happens.
FLOOR rounds optimistic numbers down towards zero.
If quantity is destructive, and significance is optimistic, FLOOR rounds away from zero.
If quantity and significance are each destructive, FLOOR rounds in the direction of zero.
For extra management over how FLOOR rounds destructive numbers, see the FLOOR.MATH perform.