Cellwise Try it free

Math function

Excel FLOOR function

FLOOR rounds a number down to the multiple you choose. =FLOOR(230,24) is 216. Use it to count the full cases you can ship, group prices into bands, or round clock times down to the hour.

=FLOOR(number, significance)

Arguments

number
The number to round down.
significance
The multiple to round down to, such as 24, 10 or "1:00". It cannot be 0.

FLOOR examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

ABCD
1ProductIn stockPer casePrice
2Sparkling water2302418.75
3Oat milk951242.5
4Coffee beans6169.99

Download this sheet as an Excel practice file (.xlsx)

  1. =FLOOR(B2,C2)

    Result: 216

    Rounds 230 bottles down to full cases of 24: 216.

  2. =FLOOR(B2,C2)/C2

    Result: 9

    Divide by the case size for the number of full cases: 9.

  3. =FLOOR(D3,10)

    Result: 40

    Puts the 42.50 price in the 40 band.

  4. =FLOOR(B3,0)

    Result: #DIV/0!

    A significance of 0 returns #DIV/0!.

How to use FLOOR

  1. Click the cell for the result.
  2. Type =FLOOR( and the number or cell.
  3. Add a comma and the multiple to round down to.
  4. Press Enter and fill down.

Common FLOOR mistakes

Getting #DIV/0!

FLOOR needs a significance other than 0. Check that the multiple cell is not empty.

Getting #NUM!

A positive number with a negative significance returns #NUM!. Give both the same sign.

Negative numbers round away from zero

=FLOOR(-2.5,2) is -4, not -2. FLOOR.MATH(-2.5,2,1) rounds toward zero instead and returns -2.

FLOOR FAQ

How do I round down to the nearest 10 in Excel?

Use =FLOOR(A2,10). For positive numbers ROUNDDOWN(A2,-1) gives the same result.

How do I count full boxes in Excel?

=INT(A2/B2) or =FLOOR(A2,B2)/B2 gives the number of full boxes, and =MOD(A2,B2) gives what is left over.

How do I round time down to the hour in Excel?

Use =FLOOR(A2,"1:00") and format the result as h:mm. 9:47 becomes 9:00.

Related functions

Updated