Exceljet

Quick, clean, and to the point

Excel FLOOR Function

Excel FLOOR function
Summary 

The Excel FLOOR function rounds a given number down to the nearest specified multiple. FLOOR works like the MROUND function, but FLOOR always rounds down.

Purpose 
Round a number down to the nearest specified multiple
Return value 
A rounded number.
Syntax 
=FLOOR (number, multiple)
Arguments 
  • number - The number that should be rounded.
  • multiple - The multiple to use when rounding.
Usage notes 

The Excel FLOOR function rounds a number down to a given multiple. FLOOR works like the MROUND function, which also rounds to a given multiple, but FLOOR always rounds down.

Example #1 - round down to nearest 5

To round a number in A1 down to the nearest multiple of 5:

=FLOOR(A1,5) // round down to nearest 5

Example #2 - round pricing down to end with .99

FLOOR can be used to set pricing after currency conversion, discounts, etc. For example, the formula below will round a number in A1 down to the nearest 1 dollar, then subtract 1 cent, to get a price like $2.99, $5.99, $49.99, etc.

=FLOOR(A1,1)-0.01

You can round pricing up to end in .99 with a similar formula based on the CEILING function:

=CEILING(A1,1)-0.01

Example #3 - round time down to nearest 15 minutes

FLOOR can be used to round time down to a given multiple. For example, to round a time in A1 down to the nearest 15 minutes, you can use FLOOR like this:

=FLOOR(A1,"0:15") // round time down to nearest 15 min

Other rounding functions in Excel

Notes

  • FLOOR works like the MROUND function, but FLOOR always rounds down.
  • If a number is already an exact multiple of significance, no rounding occurs.
  • FLOOR rounds positive numbers down toward zero.
  • FLOOR rounds negative numbers down away from zero.
  • For more control with negative numbers, see the FLOOR.MATH function.