Math & Trig
Find skipped or missing invoice numbers in a sequence
Count total skipped integers between MIN and MAX and pinpoint exact gap ranges between sorted check or invoice rows.
Audit sequential check and invoice numbering gaps
Internal audit controls verify that sequential invoice numbers have no unexplained gaps. Between MIN = 1001 and MAX = 1007, an unbroken sequence must contain 1007 - 1001 + 1 = 7 distinct integers.
Subtracting ROWS(UNIQUE(A2:A5)) (4 distinct recorded numbers) immediately flags 3 missing numbers—even if duplicate rows exist in the export. Comparing adjacent sorted rows pinpoints the exact skipped numbers (1003 and 1005–1006).
| Row | InvoiceNo (A) | Next Row Gap Check |
|---|---|---|
| 2 | 1001 | No gap (next is 1002) |
| 3 | 1002 | 1003 (1 skipped before 1004) |
| 4 | 1004 | 1005-1006 (2 skipped before 1007) |
| 5 | 1007 | (end of range) |
Total missing count and row-level gap label formulas
Sort InvoiceNo ascending first before applying the adjacent-row gap formula.
=(MAX(A2:A5) - MIN(A2:A5) + 1) - ROWS(UNIQUE(A2:A5))Pinpoint exact skipped numbers after each row
When A5 - A4 = 3 (1004 to 1007), (A4 + 1) & "-" & (A5 - 1) reports "1005-1006".
=IF(A5 - A4 <= 1, "No gap", IF(A5 - A4 = 2, "" & (A4 + 1), (A4 + 1) & "-" & (A5 - 1)))Inputs and interpretation
Use a nonempty range of numeric integers from one numbering series, with no blank or text IDs. This counts gaps only between the observed minimum and maximum; detect missing first/last numbers using separately known expected bounds. Sort ascending for adjacent labels. Numbering policies and cancelled invoices require their own business rules; a gap alone does not prove an accounting error.
Try the example
Use the original values or change them to test your understanding.