Round amounts up to the next five cents
Turn 1.21 into 1.25 when a system 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. | 121 |
| 2 | CEILING.MATH( … ,5) | Rounds the cents up to the next 5. | 125 |
| 3 | /100 | Turns the cents back into dollars. | 1.25 |
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.
=CEILING.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 amounts of zero or more, and only when your system asks for five-cent steps.
Microsoft's help: CEILING.MATH function
OTHER ROUTES YOU MIGHT LIKE

Round amounts down to five-cent steps
1.24 → 1.20
Get the template →
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 →
Show $1,234.50 instead of 1234.5 in a mail merge
1234.5 → $1,234.50
Take this route →