Find benefit money left in one currency

Turn 500.00 USD allowed and 125.50 USD used into 374.50 left.

What you'll make

In Excel: 500.00 USD allowed, 125.50 USD used becomes 374.50 left.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1OR(A8="",C8="")Checks if either amount is empty.No
2OR(B8="",D8="",B8<>D8)Checks for a missing or different currency.No, both are USD
3C8>A8Checks if more was used than allowed.No, 125.50 is less
4A8-C8Allowed amount minus used amount.374.50
5IF( … )Flags part 1, 2 or 3, else shows part 4.374.50

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

=IF(OR(A8="",C8=""),"Review missing monetary benefit",IF(OR(B8="",D8="",B8<>D8),"Review benefit currencies",IF(C8>A8,"Review used amount above allowance",A8-C8)))

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 amounts. Paste allowed and used amounts with their currency codes into the green columns of the Template tab. The amount left fills in by itself.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

This never converts currencies, so compare amounts for the same benefit and period. The amounts here are made-up.

Microsoft's help: IF function