Write a rate as about 1 in N

Turn a 12.5% rate into About 1 in 8, and a 0% rate into 0% supplied.

What you'll make

In Excel: 12.5% becomes About 1 in 8.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8=0Checks whether the rate is 0.FALSE
2,"0% supplied",The words to show for a 0 rate.Not used for row 8
3"About 1 in "&The start of the label."About 1 in "
4TEXT(1/A8,"0")Divides 1 by the rate, to a whole number."8"
5IF( … )Picks part 2 for 0, otherwise parts 3 and 4."About 1 in 8"

A 0 rate never gets divided, so it can't cause an error. Halves round up. The rates are made-up.

=IF(A8=0,"0% supplied","About 1 in "&TEXT(1/A8,"0"))

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. Paste rates, where 1 means 100%, into the green column of the Template tab.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

The label is rounded, so keep the original rate next to it. 30% becomes About 1 in 3.

Microsoft's help: TEXT function