Turn mixed percent units into rates

Use each row's unit to turn 12.5 percent points into the rate 0.125.

What you'll make

In Excel: 12.5 Percent points becomes 12.50% as a rate.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1B8The unit label for this row."Percent points"
2"Rate",A8A rate is kept as it is.Not used for row 8
3"Percent points",A8/100Percent points are divided by 100.0.125
4"Basis points",A8/10000Basis points are divided by 10000.Not used for row 8
5SWITCH( … )Uses the matching unit, or asks for a review.0.125, shown as 12.50%

Any other label says Review percentage unit, so nothing is guessed from the size.

=SWITCH(B8,"Rate",A8,"Percent points",A8/100,"Basis points",A8/10000,"Review percentage unit")

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 numbers. Paste values into column A and units into column B of the Template tab. The rates fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Write each unit exactly as Rate, Percent points or Basis points. Short forms like bps turn amber.

Microsoft's help: SWITCH function