IF

You reconcile the ledger to the client's specifications and list the difference per account. Differences of 1,5001.500 or more must be followed up; anything below that stays on the schedule without follow-up. A difference of exactly 1,5001.500 counts as follow-up.

You want a column that says Investigate or Below threshold, so the working paper shows at a glance what is left to do.

Formula in C2

=IF(B2>=1500, "Investigate", "Below threshold")

=IF(B2>=1500; "Investigate"; "Below threshold")

Result: Investigate

Watch out: An empty difference cell counts as 0 in the comparison, so an account that is not reconciled yet shows Below threshold. The Intermediate example tests for empty cells first.

You sent balance confirmations to your customers at 31 December 2025. In column C you enter the balance each customer confirmed. An empty cell means the customer has not replied yet and you still have to follow up; a 0 means the customer confirmed that they owe nothing. You want the difference with the ledger per customer, and a clear note for each customer who has not replied.

Formula in D2

=IF(C2="", "No reply", B2-C2)

=IF(C2=""; "No reply"; B2-C2)

Result: €350.00€ 350,00

Watch out: A cell that contains a space looks empty but is not, so C2="" is FALSE and the subtraction returns #VALUE!. Clear such cells with the Delete key.

=IF(ISBLANK(C2), "No reply", B2-C2)

=IF(ISBLANK(C2); "No reply"; B2-C2)

In the aging list of Veldhuis Installatie BV you recalculate the provision for doubtful debts, with a percentage of the open amount per band:

• 50% when an invoice is 90 days or more overdue
• 10% from 30 days
• 2% below that

The percentage applies to the whole open amount, not per slice. You want one formula per invoice that picks the percentage and multiplies, and an invoice at exactly 90 days must fall in the top band.

Formula in D2

=C2 * IF(B2>=90, 0.5, IF(B2>=30, 0.1, 0.02))

=C2 * IF(B2>=90; 0,5; IF(B2>=30; 0,1; 0,02))

Result: €1,200.00€ 1.200,00

Watch out: Swap the two tests and the formula still returns a number for every row, but invoices 26-0071 and 26-0054 get 10% instead of 50%, because 112>=30 is already TRUE. Nothing on the sheet looks broken.

=C2 * IFS(B2>=90, 0.5, B2>=30, 0.1, TRUE, 0.02)

=C2 * IFS(B2>=90; 0,5; B2>=30; 0,1; TRUE; 0,02)

=C2 * IF(B2<30, 0.02, IF(B2<90, 0.1, 0.5))

=C2 * IF(B2<30; 0,02; IF(B2<90; 0,1; 0,5))

You compare the year-end balance per account in the trial balance with the balance in the specification the client delivered. Performance materiality is 15,00015.000 (cell F2). Where the absolute difference exceeds it, you want the difference to follow up; otherwise 0.

An empty specification cell means the client has not delivered that specification yet, and the account must not be cleared by accident; a 0 means the specification shows a nil balance.

Formula in D2

=LET(
  ledger, B2:B7,
  spec, C2:C7,
  pm, $F$2,
  IF(spec = "", "Request specification", IF(ABS(ledger - spec) > pm, ledger - spec, 0))
)

=LET(
  ledger; B2:B7;
  spec; C2:C7;
  pm; $F$2;
  IF(spec = ""; "Request specification"; IF(ABS(ledger - spec) > pm; ledger - spec; 0))
)

Result: €26,500.00 / €0.00 / Request specification / €38,200.00 / €0.00 / -€17,500.00€ 26.500,00 / € 0,00 / Request specification / € 38.200,00 / € 0,00 / € -17.500,00

Watch out: The mirror-image mistake is testing spec = 0 instead of spec = "". The payables row still shows Request specification, but so does VAT payable, and the 38200 difference on the one account whose specification really shows nil is never followed up.

=ABS(B2:B7 - C2:C7) > $F$2

=ABS(B2:B7 - C2:C7) > $F$2