Quick answer
The FLOOR formula rounds a number down to the nearest multiple.
=FLOOR(B2,0.05)Syntax
Use this syntax as the pattern, then replace the sample arguments with your own cells, ranges, and criteria.
=FLOOR(number, significance)When to use it
Math formulas are the base of most spreadsheets. They help you total numbers, round values for reports, handle remainders, generate random values, and summarize rows without manual calculation.
- Add sales by row or category
- Round prices to clean values
- Build a running total
- Calculate discounts and adjustments
Example worksheet layout
Test the formula in a small worksheet first. This makes it easier to confirm the result before copying the formula down hundreds of rows.
| Column or range | What it represents |
|---|---|
| A | Category or row label |
| B | Input value or amount |
| C | Calculated result |
Copy-paste examples
These examples move from simple to more practical versions. Start with the beginner examples, confirm the result, then adapt the intermediate and advanced versions.
Basic FLOOR example
=FLOOR(B2,0.05)Use the simple version first and compare the result to a manual check.
Copy down a table
=FLOOR(B3,0.05)After testing the first row, copy the formula down the rest of the column.
Use a fixed input cell
=FLOOR(B2,0.05)Lock assumptions, targets, or thresholds with dollar signs when needed.
Use an Excel Table
=FLOOR(B2,0.05)Tables make formulas more readable and resilient.
Make the formula report-safe
=IFERROR(FLOOR(B2,0.05),"")Avoid visible errors in a final report when inputs are missing.
Use LET for clear logic
=LET(result,FLOOR(B2,0.05),result)LET helps document the calculation inside the formula itself.
How to use it step by step
- Put your raw data in clear columns with headers such as Amount, Date, Status, Region, or Customer.
- Paste the starting formula into the first result cell, usually row 2.
- Replace sample references such as A2, B2, or $F$2:$F$100 with the cells in your own worksheet.
- Check the result on a few rows where you already know the expected answer.
- Copy the formula down only after the first row returns the right result.
Common mistakes and fixes
- The referenced range does not include all rows.
- The cell format makes the result look wrong even when the formula is correct.
- A fixed assumption cell was copied down without dollar signs.
- Test the formula on a few known rows before using it across a full report.
Beginner tips
- Use a small sample first so you can see exactly how the FLOOR formula behaves.
- Convert your source range into an Excel Table when the data will grow over time.
- Add a short note near important formulas so future users know what each input represents.