Round amounts down to five-cent steps
Turn 1.24 into 1.20 when a rule needs amounts in steps of five cents.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | ROUND(A8*100,0) | Turns the amount into whole cents. | 124 |
| 2 | FLOOR.MATH( … ,5) | Rounds the cents down to a step of 5. | 120 |
| 3 | /100 | Turns the cents back into dollars. | 1.20 |
Parts 1 and 2 work from the inside out, then part 3 divides. Amounts already on five cents stay the same. The amounts are made-up.
=FLOOR.MATH(ROUND(A8*100,0),5)/100How to use it
Yellow bar at the top? Click Enable Editing.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own amounts. Paste amounts, as numbers, into the green column of the Template tab. The results fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Use this for whole-cent amounts of zero or more, and only when your rule asks for five-cent steps.
Microsoft's help: FLOOR.MATH function
OTHER ROUTES YOU MIGHT LIKE

Set tiny amounts within tolerance to zero
0.004 within 0.01 → 0
Get the template →
Round amounts to set decimal places
123.456 to 2 decimals → 123.46
Get the template →
Cut an amount to cents without rounding
1.999 → $1.99
Get the template →
Show $1,234.50 instead of 1234.5 in a mail merge
1234.5 → $1,234.50
Take this route →