Combination
Differences above the reporting threshold with LET, FILTER, HSTACK and ABS
Reconcile the ledger to the client's specification per customer and list only the differences above the reporting threshold: LET names the differences once, HSTACK pairs them with the customer, FILTER keeps the rows where ABS exceeds the threshold.
Functions used:LETFILTERHSTACKABSExcel 2024 or later (This combination needs Excel 2024 or later)
Difficulty rating 4 of 5
Worked example
Receivables differences above the threshold
Scenario
For the audit of Veldhuis Installatie BV you compare the receivables balance per customer in the general ledger with the client's aging list at 31 December 2025. The reporting threshold is set at 2,5002.500. You want a list of only the customers whose difference is larger than that, in either direction, to ask the client about.
The data
| Row | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Relation | Ledger | Specification | Reporting threshold | €2,500.00€ 2.500,00 | ||
| 2 | D1001 | €18,450.00€ 18.450,00 | €18,450.00€ 18.450,00 | ||||
| 3 | D1002 | €12,300.00€ 12.300,00 | €14,800.00€ 14.800,00 | D1004=LET(diff, B2:B7-C2:C7, FILTER(HSTACK(A2:A7, diff), ABS(diff)>F1, "No differences above threshold"))=LET(diff; B2:B7-C2:C7; FILTER(HSTACK(A2:A7; diff); ABS(diff)>F1; "No differences above threshold")) | €4,500.00€ 4.500,00 | ||
| 4 | D1003 | €9,875.00€ 9.875,00 | €9,575.00€ 9.575,00 | D1006 | -€2,930.00€ -2.930,00 | ||
| 5 | D1004 | €25,600.00€ 25.600,00 | €21,100.00€ 21.100,00 | ||||
| 6 | D1005 | €7,420.00€ 7.420,00 | €7,420.00€ 7.420,00 | ||||
| 7 | D1006 | €16,050.00€ 16.050,00 | €18,980.00€ 18.980,00 | ||||
| 8 |
Swipe sideways to see all columns
Formula in E3
=LET(diff,B2:B7-C2:C7, FILTER(HSTACK(A2:A7, diff), ABS(diff)>F1, "No differences above threshold"))
=LET(diff;B2:B7-C2:C7; FILTER(HSTACK(A2:A7; diff); ABS(diff)>F1; "No differences above threshold"))
LETNames intermediate values so a formula reads clearly and computes each once.Read more about LETPress Escape to close
name1The name you give the first value. Must start with a letter.Variable defined in this LETBound to:
B2:B7-C2:C7Press Escape to closename_value1The value or expression that name1 stands for.Ledger column, 6 cellsPress Escape to close
-SubtractsPress Escape to close
ReferenceSpecification column, 6 cellsPress Escape to close
FILTERReturns every row of a range that meets a condition.Used here as: calculation_or_name2The final calculation, or the next name to define.Read more about FILTERPress Escape to close
HSTACK HOR.STAPELENPlaces ranges side by side.Used here as: arrayThe rows you want back: one column or a whole table.Read more about HSTACKPress Escape to close
array1The first range or array; it ends up in the leftmost columns.Relation column, 6 cellsPress Escape to close
array2 (optional)Another range or array to place to the right; add as many as you need.Variable defined in this LETBound to:
B2:B7-C2:C7Press Escape to closeABSThe value without its sign.Used here as: includeOne TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.Read more about ABSPress Escape to close
numberThe number, cell or calculation whose sign you want removed.Variable defined in this LETBound to:
B2:B7-C2:C7Press Escape to close>Greater thanPress Escape to close
ReferenceInput cell: €2,500.00€ 2.500,00Press Escape to close
if_empty (optional)What to show when no row passes; without it you get #CALC!.Press Escape to close
Result: D1004 | €4,500.00 / D1006 | -€2,930.00D1004 | € 4.500,00 / D1006 | € -2.930,00
How the pieces fit together
The formula taken apart, from the inside out. Each part can be typed into its own cell to see what it returns.
=B2:B7-C2:C7
=B2:B7-C2:C7
ReferenceLedger column, 6 cellsPress Escape to close-SubtractsPress Escape to closeReferenceSpecification column, 6 cellsPress Escape to closeSubtracts the specification from the ledger, customer by customer. The result is one column of six differences: 0, -2,500, 300, 4,500, 0 and -2,930.
=ABS(B2:B7-C2:C7)>F1
=ABS(B2:B7-C2:C7)>F1
numberThe number, cell or calculation whose sign you want removed.Ledger column, 6 cellsPress Escape to close-SubtractsPress Escape to closeReferenceSpecification column, 6 cellsPress Escape to close>Greater thanPress Escape to closeReferenceInput cell: €2,500.00€ 2.500,00Press Escape to closeABS removes the sign, so a shortfall of -2,930 counts as 2,930. Compared with the threshold of 2,500 in
F1this gives FALSE, FALSE, FALSE, TRUE, FALSE, TRUE.D1002is exactly 2,500 and is not above it.=HSTACK(A2:A7,
B2:B7-C2:C7) =HSTACK(A2:A7;
B2:B7-C2:C7) array1The first range or array; it ends up in the leftmost columns.Relation column, 6 cellsPress Escape to closearray2 (optional)Another range or array to place to the right; add as many as you need.Ledger column, 6 cellsPress Escape to close-SubtractsPress Escape to closeReferenceSpecification column, 6 cellsPress Escape to closeHSTACK places the customer codes next to the differences: a table of 6 rows and 2 columns, from
D1001with 0 down toD1006with -2,930.=FILTER(HSTACK(A2:A7,
B2:B7-C2:C7), ABS(B2:B7-C2:C7)>F1, "No differences above threshold") =FILTER(HSTACK(A2:A7;
B2:B7-C2:C7); ABS(B2:B7-C2:C7)>F1; "No differences above threshold") FILTERReturns every row of a range that meets a condition.Read more about FILTERPress Escape to closeHSTACK HOR.STAPELENPlaces ranges side by side.Used here as: arrayThe rows you want back: one column or a whole table.Read more about HSTACKPress Escape to closearray1The first range or array; it ends up in the leftmost columns.Relation column, 6 cellsPress Escape to closearray2 (optional)Another range or array to place to the right; add as many as you need.Ledger column, 6 cellsPress Escape to close-SubtractsPress Escape to closeReferenceSpecification column, 6 cellsPress Escape to closeABSThe value without its sign.Used here as: includeOne TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.Read more about ABSPress Escape to closenumberThe number, cell or calculation whose sign you want removed.Ledger column, 6 cellsPress Escape to close-SubtractsPress Escape to closeReferenceSpecification column, 6 cellsPress Escape to close>Greater thanPress Escape to closeReferenceInput cell: €2,500.00€ 2.500,00Press Escape to closeif_empty (optional)What to show when no row passes; without it you get #CALC!.Press Escape to closeFILTER keeps only the rows of the table where the TRUE sits:
D1004with 4,500 andD1006with -2,930. The difference is calculated twice here, which is why LET is worth it.=LET(diff,
B2:B7-C2:C7, FILTER(HSTACK(A2:A7, diff), ABS(diff)>F1, "No differences above threshold")) =LET(diff;
B2:B7-C2:C7; FILTER(HSTACK(A2:A7; diff); ABS(diff)>F1; "No differences above threshold")) LETNames intermediate values so a formula reads clearly and computes each once.Read more about LETPress Escape to closename1The name you give the first value. Must start with a letter.Variable defined in this LETBound to:B2:B7-C2:C7Press Escape to closename_value1The value or expression that name1 stands for.Ledger column, 6 cellsPress Escape to close-SubtractsPress Escape to closeReferenceSpecification column, 6 cellsPress Escape to closeFILTERReturns every row of a range that meets a condition.Used here as: calculation_or_name2The final calculation, or the next name to define.Read more about FILTERPress Escape to closeHSTACK HOR.STAPELENPlaces ranges side by side.Used here as: arrayThe rows you want back: one column or a whole table.Read more about HSTACKPress Escape to closearray1The first range or array; it ends up in the leftmost columns.Relation column, 6 cellsPress Escape to closearray2 (optional)Another range or array to place to the right; add as many as you need.Variable defined in this LETBound to:B2:B7-C2:C7Press Escape to closeABSThe value without its sign.Used here as: includeOne TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.Read more about ABSPress Escape to closenumberThe number, cell or calculation whose sign you want removed.Variable defined in this LETBound to:B2:B7-C2:C7Press Escape to close>Greater thanPress Escape to closeReferenceInput cell: €2,500.00€ 2.500,00Press Escape to closeif_empty (optional)What to show when no row passes; without it you get #CALC!.Press Escape to closeLET calculates B2:B7-C2:C7 once under the name diff and uses it twice. The result is the same two rows:
D10044,500 andD1006-2,930. If nothing exceeds the threshold, the message appears instead.
Step by step
- LET stores B2:B7-C2:C7 as diff: 18,45018.450-18,45018.450 = 0; 12,30012.300-14,80014.800 = -2,5002.500; 9,8759.875-9,5759.575 = 300; 25,60025.600-21,10021.100 = 4,5004.500; 7,4207.420-7,4207.420 = 0; 16,05016.050-18,98018.980 = -2,9302.930.
- ABS(diff) gives 0, 2,5002.500, 300, 4,5004.500, 0 and 2,9302.930, so every difference is now positive.
- Comparing with
F1(2,5002.500): 0 is not above, 2,5002.500 is not above (equal), 300 is not above, 4,5004.500 is above, 0 is not above, 2,9302.930 is above. That gives FALSE, FALSE, FALSE, TRUE, FALSE, TRUE. - HSTACK(A2:A7, diff) builds a table of 6 rows:
D10010,D1002-2,5002.500,D1003300,D10044,5004.500,D10050,D1006-2,9302.930. The signed difference is kept, not the ABS value. - FILTER keeps rows 4 and 6, where the test is TRUE:
D1004with 4,5004.500 (ledger higher) andD1006with -2,9302.930 (ledger lower). The result spills overE3:F4.
Why it works
The difference is needed twice: as a value to show and as a value to test. LET calculates it once and gives it a name, so the two uses cannot drift apart.
ABS makes the test work for overstatements and understatements alike, while the table still shows the sign, which tells you in which direction to ask the client. FILTER does the selection and the last argument covers the case that everything stays under the limit.
Watch out for
- Without ABS, the test diff>F1 only finds positive differences.
D1006at -2,9302.930 would drop out silently, and that is exactly a customer where the ledger is lower than the client's own list. - The test is strictly above the threshold.
D1002differs by exactly 2,5002.500 and is not listed. If your file defines the threshold as 'up to and including', or you want to see the borderline cases, use >=. - Both lists must have the customers in the same order. This formula subtracts row by row and does not match on customer code. If the specification is sorted differently, look up the specification first (for example with XLOOKUP on column
A).
Variations
=FILTER(A2:C7,ABS(B2:B7-C2:C7)>F1, "No differences above threshold")
=FILTER(A2:C7;ABS(B2:B7-C2:C7)>F1; "No differences above threshold")
FILTERReturns every row of a range that meets a condition.Read more about FILTERPress Escape to close
arrayThe rows you want back: one column or a whole table.6 rows by 3 columnsPress Escape to close
ABSThe value without its sign.Used here as: includeOne TRUE/FALSE per row, as tall as array (as wide for columns): what to keep.Read more about ABSPress Escape to close
numberThe number, cell or calculation whose sign you want removed.Ledger column, 6 cellsPress Escape to close
-SubtractsPress Escape to close
ReferenceSpecification column, 6 cellsPress Escape to close
>Greater thanPress Escape to close
ReferenceInput cell: €2,500.00€ 2.500,00Press Escape to close
if_empty (optional)What to show when no row passes; without it you get #CALC!.Press Escape to close
Without LET and HSTACK: FILTER on the source columns. It lists D1004 and D1006 with ledger and specification (25,60025.600 and 21,10021.100; 16,05016.050 and 18,98018.980), but not the difference itself.
=SUMPRODUCT(--(ABS(B2:B7-C2:C7)>F1))
=SUMPRODUCT(--(ABS(B2:B7-C2:C7)>F1))
SUMPRODUCT SOMPRODUCTMultiplies ranges pairwise and adds the results.Read more about SUMPRODUCTPress Escape to close
array1The first range or array; every other array must have the same size.Makes the value negative; two in a row turn TRUE and FALSE into 1 and 0Press Escape to close
-Makes the value negative; two in a row turn TRUE and FALSE into 1 and 0Press Escape to close
numberThe number, cell or calculation whose sign you want removed.Ledger column, 6 cellsPress Escape to close
-SubtractsPress Escape to close
ReferenceSpecification column, 6 cellsPress Escape to close
>Greater thanPress Escape to close
ReferenceInput cell: €2,500.00€ 2.500,00Press Escape to close
The older way to count: SUMPRODUCT with a double minus turns the TRUE values into 1s. It returns 2, the number of differences above the threshold, but not which customers they are.
=SUMPRODUCT((ABS(B2:B7-C2:C7)>F1)*(B2:B7-C2:C7))
=SUMPRODUCT((ABS(B2:B7-C2:C7)>F1)*(B2:B7-C2:C7))
SUMPRODUCT SOMPRODUCTMultiplies ranges pairwise and adds the results.Read more about SUMPRODUCTPress Escape to close
ABSThe value without its sign.Used here as: array1The first range or array; every other array must have the same size.Read more about ABSPress Escape to close
numberThe number, cell or calculation whose sign you want removed.Ledger column, 6 cellsPress Escape to close
-SubtractsPress Escape to close
ReferenceSpecification column, 6 cellsPress Escape to close
>Greater thanPress Escape to close
ReferenceInput cell: €2,500.00€ 2.500,00Press Escape to close
*MultipliesPress Escape to close
ReferenceLedger column, 6 cellsPress Escape to close
-SubtractsPress Escape to close
ReferenceSpecification column, 6 cellsPress Escape to close
The net total of the differences above the threshold: 4,5004.500 + -2,9302.930 = 1,5701.570. Useful as a control total next to the list.
Related combinations
Functions used
- LETGives a value or an intermediate result a name inside one formula, so you can reuse it by name and Excel calculates it only once.
- FILTERReturns every row of a range that meets your condition, as a live list that spills into the cells below and updates when the data changes.
- HSTACKPlaces ranges or arrays next to each other, left to right, into one spilled block that updates when the sources change.
- ABSReturns a number without its sign, so a shortfall and an overshoot of the same size become the same distance from zero.
Functions used
- LETGives a value or an intermediate result a name inside one formula, so you can reuse it by name and Excel calculates it only once.
- FILTERReturns every row of a range that meets your condition, as a live list that spills into the cells below and updates when the data changes.
- HSTACKPlaces ranges or arrays next to each other, left to right, into one spilled block that updates when the sources change.
- ABSReturns a number without its sign, so a shortfall and an overshoot of the same size become the same distance from zero.