Turn mixed percent units into rates
Use each row's unit to turn 12.5 percent points into the rate 0.125.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | B8 | The unit label for this row. | "Percent points" |
| 2 | "Rate",A8 | A rate is kept as it is. | Not used for row 8 |
| 3 | "Percent points",A8/100 | Percent points are divided by 100. | 0.125 |
| 4 | "Basis points",A8/10000 | Basis points are divided by 10000. | Not used for row 8 |
| 5 | SWITCH( … ) | 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.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own numbers. Paste values into column A and units into column B of the Template tab. The rates fill in by themselves.
- 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
OTHER ROUTES YOU MIGHT LIKE

Turn a 2x multiplier into percent change
2x multiplier → 100% change
Get the template →
Round a rate to chosen percent decimals
12.34560% at 2 percent decimals → 12.3500%
Get the template →
Round rates down to percent decimals
12.3456% to 2 decimals → 12.34%
Get the template →
Merge a few test letters before the whole mailing
Whole mailing → records 2 to 4 only
Take this route →