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

In Excel: 12.33 becomes 12.35.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8The amount, like 12.33.12.33
2ROUND( … *100,0)Turns it into whole cents.1233
3ROUND( … /5,0)Counts five-cent steps, to the nearest whole step.247
4*5/100Turns 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/100

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own amounts. Paste amounts into the green column of the Template tab. The rounded amounts fill in by themselves.
  3. 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