How to Use FLOOR, FLOOR.MATH, FLOOR.PRECISE Function in Excel + Tips

Hello everyone! Today i will show you how to use FLOOR, FLOOR.MATH, and FLOOR.PRECISE functions in Microsoft Excel step by step.

What is FLOOR Function in Excel?

The FLOOR function is a Math formula in Excel that help you round a number DOWN to the nearest multiple of significance (or nearest step number like 5, 10, 0.05, etc.).

Unlike normal ROUND formula that can round up or down, FLOOR always rounds number DOWN towards zero (or lower multiple)!

Excel have 3 variation of Floor formula:

FLOOR: Traditional floor formula (needs both number and significance).
FLOOR.MATH: Newer version in Excel 2013+. Can round negative numbers towards or away from zero with mode option.
FLOOR.PRECISE: Round down number regardless of sign, default significance is 1.

📚 Related Tutorial: Want to round numbers up instead? Check out How to Use CEILING Function in Excel!

🛠️ Formula Syntax & Parameters

1. FLOOR Formula Syntax

=FLOOR(number, significance)

number: Required. The number or cell reference you want to round down.
significance: Required. The multiple value you want to round down to (example: 5, 10, 0.05).

2. FLOOR.MATH Formula Syntax

=FLOOR.MATH(number, [significance], [mode])

number: Required. The number to round down.
[significance]: Optional. The multiple to round to (Default is 1).
[mode]: Optional. For negative numbers only:
- 0 (or omitted): Rounds away from zero (more negative).
- 1 (or non-zero): Rounds towards zero (closer to zero).

3. FLOOR.PRECISE Formula Syntax

=FLOOR.PRECISE(number, [significance])

number: Required. The value to round down.
[significance]: Optional. Multiple to round (Default is 1).

📋 Sample Data & Simple Examples

Example 1: Round Currency Down to Nearest $0.05 or $0.50

Suppose Cell A2 = $4.87:

=FLOOR(A2, 0.05)

👉 Result: $4.85
(Because $4.85 is the nearest multiple of $0.05 below $4.87!)

Suppose Cell A2 = $12.75 and you want nearest $0.50:

=FLOOR(A2, 0.50)

👉 Result: $12.50

Example 2: Calculate Full Packets / Complete Boxes

Imagine you sell items in boxes of 6. You have 23 items in cell A2 and want to know how many full items can fit into complete boxes:

=FLOOR(A2, 6)

👉 Result: 18 items
(Because 18 is the highest multiple of 6 that fits inside 23 items!)

To find total full boxes you can ship:

=FLOOR(A2, 6) / 6

👉 Result: 3 full boxes (5 items leftover).

Example 3: Compare Negative Numbers in FLOOR.MATH

Suppose Cell A2 = -4.5:

=FLOOR.MATH(-4.5, 1, 0)

👉 Result: -5 (Rounds away from zero / more negative)

=FLOOR.MATH(-4.5, 1, 1)

👉 Result: -4 (Rounds towards zero / closer to zero)

🎥 FLOOR Function Video Tutorial (Khmer)

Follow this step-by-step video guide to learn FLOOR, FLOOR.MATH, and FLOOR.PRECISE in Excel:

FLOOR vs CEILING Comparison Video Tutorial

Watch this extra video to see how FLOOR (round DOWN) works together with CEILING (round UP):

💡 Quick Tips for Using FLOOR Functions

FLOOR vs CEILING: Remember simple rule: FLOOR rounds DOWN to ground level, while CEILING rounds UP to upper roof!
FLOOR vs INT / TRUNC: INT or TRUNC only rounds down to integer whole numbers, but FLOOR lets you round down to any step like 0.25, 5, 10, or 50.
Significance Sign Error in Classic FLOOR: In old =FLOOR(number, significance), if number is negative and significance is positive, Excel returns #NUM! error. Use FLOOR.MATH or FLOOR.PRECISE to fix this!
Default Significance is 1 in FLOOR.MATH: If you omit significance in =FLOOR.MATH(5.9), Excel automatically assumes significance is 1 and returns 5.
Great for Full Quantity & Pricing: Use FLOOR when calculating complete packages, full work shifts, or discount prices rounding down to lower step.