Round rates up to percent decimals

Turn 12.3456% into 12.35% so a rate is never understated. Negatives move toward zero.

What you'll make

In Excel: 12.3456% to 2 decimals becomes 12.35%.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8The rate, such as 12.3456%.12.3456%
210^(-B8-2)The step size: 0.01% for 2 percent decimals.0.01%
3CEILING.MATH( … )Rounds up to that step, so negatives move toward zero.12.3500%

The -2 is there because a rate of 1 is 100%. This changes the stored rate, not only how it looks. The rates are made-up.

=CEILING.MATH(A8,10^(-B8-2))

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 rates. On the Template tab, paste rates into column A. Put how many percent decimals to keep, 0 to 4, into column B.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Use this only when your rule says to round rates up. Keep the original rates too.

Microsoft's help: CEILING.MATH function