LET

You recalculate the VAT on sales invoices of Veldhuis Installatie BV and compare the gross amount with what the customer paid. The net amount and the VAT rate are in each row. The net amount is needed twice, once to work out the VAT and once as the amount the VAT is added to, so you give it a name, and the VAT amount gets a name too so the last step reads as net + vat.

Formula in D2

=LET(net, B2, vat, net * C2, net + vat)

=LET(net; B2; vat; net * C2; net + vat)

Result: €22,082.50€ 22.082,50

Watch out: A formula that ends on a pair, such as =LET(net, B2, vat, net * C2), has no calculation to return and Excel rejects it. Always finish with the expression you want to see.

=B2 * (1 + C2)

=B2 * (1 + C2)

You reconcile three ledger balances of Veldhuis Installatie BV to the client's specifications. For each account you work out the difference and its share of the specification, and flag the account when that share is above the threshold in G1.

The share gets its own step and a name next to the difference, so the test reads as share compared with the threshold. One difference is negative, because the ledger is lower than the specification.

Formula in D2

=LET(diff, B2 - C2, share, diff / C2, IF(share > $G$1, "Investigate", "Within threshold"))

=LET(diff; B2 - C2; share; diff / C2; IF(share > $G$1; "Investigate"; "Within threshold"))

Result: Investigate

Watch out: Ledger lower than the specification gives a negative share, and a negative share is never above 0.5%, so a large shortfall such as -20,00020.000 passes as Within threshold. Test the absolute value to catch both directions.

=LET(diff, B2 - C2, share, ABS(diff) / C2, IF(share > $G$1, "Investigate", "Within threshold"))

=LET(diff; B2 - C2; share; ABS(diff) / C2; IF(share > $G$1; "Investigate"; "Within threshold"))

In the purchase journal of Veldhuis Installatie BV you look for invoices booked twice, as a duplicate means a possible double payment. The same invoice number may legitimately come from two different suppliers, so a duplicate is the same invoice number from the same supplier. You want the duplicate lines on a list.

Formula in E2

=LET(invoice, A2:A8, supplier, B2:B8, FILTER(A2:C8, COUNTIFS(invoice, invoice, supplier, supplier) > 1, "No duplicates"))

=LET(invoice; A2:A8; supplier; B2:B8; FILTER(A2:C8; COUNTIFS(invoice; invoice; supplier; supplier) > 1; "No duplicates"))

Result: INV-3302 | Jansen Logistiek BV | €1,512.50 / INV-3303 | Bakker Staal BV | €7,260.00 / INV-3302 | Jansen Logistiek BV | €1,512.50 / INV-3303 | Bakker Staal BV | €7,260.00INV-3302 | Jansen Logistiek BV | € 1.512,50 / INV-3303 | Bakker Staal BV | € 7.260,00 / INV-3302 | Jansen Logistiek BV | € 1.512,50 / INV-3303 | Bakker Staal BV | € 7.260,00

Watch out: Testing the invoice number alone, COUNTIF(invoice, invoice) > 1, silently adds INV-3301 twice, because two different suppliers used the same number. The list looks plausible but 2 of its lines are not duplicates.

=FILTER(A2:C8, COUNTIFS(A2:A8, A2:A8, B2:B8, B2:B8) > 1, "No duplicates")

=FILTER(A2:C8; COUNTIFS(A2:A8; A2:A8; B2:B8; B2:B8) > 1; "No duplicates")

You test the allowance for doubtful debts at Veldhuis Installatie BV at the balance date of 31 December 2025. The client applies these percentages:

• 0% to invoices not yet due
• 1% up to 30 days overdue
• 5% for 31 to 60 days
• 20% for 61 to 90 days
• 50% above 90 days

You recalculate the total allowance to compare with the booked amount. The formula must be readable for the reviewer and let you check each step.

Formula in F3

=LET(
  balance_date, F1,
  open_amount, B2:B8,
  due_date, C2:C8,
  days_over, balance_date - due_date,
  rate, IF(days_over > 90, 0.5, IF(days_over > 60, 0.2, IF(days_over > 30, 0.05, IF(days_over > 0, 0.01, 0)))),
  allowance, open_amount * rate,
  ROUND(SUM(allowance), 2)
)

=LET(
  balance_date; F1;
  open_amount; B2:B8;
  due_date; C2:C8;
  days_over; balance_date - due_date;
  rate; IF(days_over > 90; 0,5; IF(days_over > 60; 0,2; IF(days_over > 30; 0,05; IF(days_over > 0; 0,01; 0))));
  allowance; open_amount * rate;
  ROUND(SUM(allowance); 2)
)

Result: €16,787.50€ 16.787,50

Watch out: The mirror-image mistake is using >= where the band says 'up to 30 days'. With days_over >= 30 invoice 25-1106 moves to the 5% band: 4,2504.250 × 0.05 = 212.50 instead of 42.50, and the total becomes 16,957.5016.957,50. The result still looks reasonable.

=LET(
  balance_date, F1,
  open_amount, B2:B8,
  due_date, C2:C8,
  days_over, balance_date - due_date,
  rate, IF(days_over > 90, 0.5, IF(days_over > 60, 0.2, IF(days_over > 30, 0.05, IF(days_over > 0, 0.01, 0)))),
  rate
)

=LET(
  balance_date; F1;
  open_amount; B2:B8;
  due_date; C2:C8;
  days_over; balance_date - due_date;
  rate; IF(days_over > 90; 0,5; IF(days_over > 60; 0,2; IF(days_over > 30; 0,05; IF(days_over > 0; 0,01; 0))));
  rate
)