Cap a progress rate at 100%

Turn 45 done of a 40 goal into 100%, not 113%, and 18 of 24 into 75%.

What you'll make

In Excel: 45 done of a 40 goal becomes 100%.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1B8=0Checks whether the goal is 0.FALSE
2,"Review zero goal",The message for a 0 goal.Not used for row 8
3A8/B8Divides the completed count by the goal.1.125 (113%)
4MAX(0,MIN(1, … ))Keeps the rate between 0% and 100%.1 (100%)
5IF( … )Picks part 2 for a 0 goal, otherwise part 4.100%

A 0 goal is never divided, so it can't cause an error. The counts are made-up.

=IF(B8=0,"Review zero goal",MAX(0,MIN(1,A8/B8)))

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 counts. Paste completed counts into column A and goal counts into column B of the Template tab.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Capped progress hides any extra work, so keep the real counts next to it.

Microsoft's help: MIN function