Round amounts to the nearest five cents
Turn 12.33 into 12.35 when a rule asks for five-cent steps.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | A8 | The amount, like 12.33. | 12.33 |
| 2 | ROUND( … *100,0) | Turns it into whole cents. | 1233 |
| 3 | ROUND( … /5,0) | Counts five-cent steps, to the nearest whole step. | 247 |
| 4 | *5/100 | Turns the steps back into dollars. | 12.35 |
Each bracket wraps the one above it, so Excel works from the inside out. Amounts already on a five-cent step stay the same. The amounts are made-up.
=ROUND(ROUND(A8*100,0)/5,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 into the green column of the Template tab. The rounded amounts fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Use this only for whole-cent amounts of zero or more, and only when your rule asks for five-cent steps.
Microsoft's help: ROUND function



