Count visits left without hiding overuse

Turn 20 allowed and 6 used into 14 visits left. Overuse shows Review, not 0.

What you'll make

In Excel: 20 allowed, 6 used becomes 14 left.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1OR(A8="",B8="")Checks if either count is empty.No
2B8>A8Checks if more visits were used than allowed.No, 6 is not more than 20
3A8-B8Visits allowed minus visits used.14
4IF( … )Flags part 1 or 2, else shows part 3.14

A Review message shows when part 1 or part 2 says yes. Otherwise the row shows the visits left.

=IF(OR(A8="",B8=""),"Review missing visit values",IF(B8>A8,"Review used visits above allowance",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 visits allowed and visits used, as whole numbers, into the green columns of the Template tab. The visits left fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Put counts side by side only when they are for the same benefit, network and period. The counts here are made-up.

Microsoft's help: IF function