Turn 1 in 20 into a 5% rate
Read text like 1 in 20 as the rate 0.05, shown as 5.0%.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | TEXTAFTER(A8,"1 in ") | Takes the text after 1 in. | "20" |
| 2 | VALUE( … ) | Turns that text into a number, called n. | 20 |
| 3 | n<=0,"Review: …", | Flags a number of 0 or below. | Not used for row 8 |
| 4 | 1/n | Divides 1 by the number: one in every n. | 0.05 |
| 5 | LET( … ) | Gives the rate, shown as a percent. | 5.0% |
Parts 1 and 2 work from the inside out, then the IF checks the number. The frequencies are made-up.
=LET(n,VALUE(TEXTAFTER(A8,"1 in ")),IF(n<=0,"Review: 0 or below",1/n))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 frequencies. Paste text like 1 in 20 into the green column 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
This is the rate your source states, not a new estimate. Keep the source text with it.
Microsoft's help: TEXTAFTER function
OTHER ROUTES YOU MIGHT LIKE

Read text like 12.5 percent as a rate
12.5 percent → 0.125
Get the template →
Turn mixed percent units into rates
12.5 Percent points → 12.50% as a rate
Get the template →
Turn a 2x multiplier into percent change
2x multiplier → 100% change
Get the template →
Merge a few test letters before the whole mailing
Whole mailing → records 2 to 4 only
Take this route →