Auditing & Error-Proofing: Flag Payments Just Under The Approval Limit

Excel Formulas › Auditing & Error-Proofing

Payments clustered just below an approval limit can mean someone is avoiding review. An IF with AND checks whether an amount falls inside a narrow band under the limit, so only the suspicious ones are listed for follow-up.

Functions used (tap for the full reference guide):

The example

Three worked examples. Change any yellow input in the practice workbook and the results recalculate.

The formula

Here is the formula, built directly from the inputs:

How it works

Here is the logic, one piece at a time:

1. B2<C2 keeps amounts under the limit.
2. C2*(1-D2) is the bottom of the band.
3. AND requires both.
4. C2-B2 shows the gap.

Keep every number in its own input cell so the formula never needs editing.

Try it: interactive demo

Variations

Count flagged

Total flagged payments.

Same vendor total

Sum by vendor.

Pitfalls & errors

Practice workbook

Frequently asked questions

Related formulas: Reconcile Two Lists (Find Mismatches) · Cross-Foot Check (Totals Tie) · Flag Out-of-Range Values

Function references: IFAND