IFS

You have tested five controls at Veldhuis Installatie BV and noted the deviation rate per control. The audit approach says:

• up to and including 5%: you rely on the control
• above 5% up to and including 10%: you extend the sample
• above 10%: you do not rely on it

You want one formula that gives each control its conclusion.

Formula in D2

=IFS(B2<=0.05, "Rely on control", B2<=0.1, "Extend sample", B2>0.1, "Do not rely")

=IFS(B2<=0,05; "Rely on control"; B2<=0,1; "Extend sample"; B2>0,1; "Do not rely")

Result: Extend sample

Watch out: Delete the third pair and row 6 (14%) shows #N/A: no condition is TRUE, so there is nothing to return. The Intermediate example shows a catch-all that rules this out.

=IFS(B2<=0.05, "Rely on control", B2<=0.1, "Extend sample", TRUE, "Do not rely")

=IFS(B2<=0,05; "Rely on control"; B2<=0,1; "Extend sample"; TRUE; "Do not rely")

On the summary of unadjusted differences for Veldhuis Installatie BV you list five items. A negative amount means the ledger is lower than it should be. In this example the firm's approach is, by size of the difference:

• from overall materiality (F1) upward: needs an adjustment
• from performance materiality (F2) upward: is discussed with management
• from the clearly inconsequential threshold (F3) upward: goes on the summary
• anything below that: is clearly inconsequential

Every misstatement above the clearly inconsequential threshold is communicated with a request to correct it; the tiers here only set how it is followed up. The size counts, not the sign.

Formula in C2

=IFS(ABS(B2)>=$F$1, "Request adjustment", ABS(B2)>=$F$2, "Discuss with management", ABS(B2)>=$F$3, "Record on summary", TRUE, "Clearly inconsequential")

=IFS(ABS(B2)>=$F$1; "Request adjustment"; ABS(B2)>=$F$2; "Discuss with management"; ABS(B2)>=$F$3; "Record on summary"; TRUE; "Clearly inconsequential")

Result: Discuss with management

Watch out: Leave out ABS and every negative difference falls through to the catch-all, because -88,00088.000 is not >= 120,000120.000, 84,00084.000 or 6,0006.000. The cell shows no error: Clearly inconsequential looks plausible, which makes this the dangerous mistake.

=LET(size, ABS(B2), IFS(size>=$F$1, "Request adjustment", size>=$F$2, "Discuss with management", size>=$F$3, "Record on summary", TRUE, "Clearly inconsequential"))

=LET(size; ABS(B2); IFS(size>=$F$1; "Request adjustment"; size>=$F$2; "Discuss with management"; size>=$F$3; "Record on summary"; TRUE; "Clearly inconsequential"))

At Veldhuis Installatie BV you test whether purchase invoices around 31 December 2025 sit in the right year. The invoice date shows when the cost was booked, the delivery date shows when the goods came in. Three cases matter:

• a missing delivery date cannot be judged and must be requested
• delivery on or before the balance sheet date but invoice dated after it: a liability is missing
• delivery after the balance sheet date but invoice dated on or before it: the cost is booked too early

Formula in D2

=IFS(C2="", "Delivery date missing", AND(C2<=$F$1, B2>$F$1), "Liability missing: cost belongs to 2025", AND(C2>$F$1, B2<=$F$1), "Booked too early: delivery in 2026", TRUE, "Correct period")

=IFS(C2=""; "Delivery date missing"; AND(C2<=$F$1; B2>$F$1); "Liability missing: cost belongs to 2025"; AND(C2>$F$1; B2<=$F$1); "Booked too early: delivery in 2026"; TRUE; "Correct period")

Result: Liability missing: cost belongs to 2025

Watch out: Move the empty-cell check to the end and PI-7105 is shown as Correct period without any error: the empty C6 is read as the date 0, which is before 31 Dec 2025, so neither date condition catches it and the TRUE pair does. The test says an invoice is fine when you have not seen the delivery date.

=IFS(C2="", "Delivery date missing", (C2<=$F$1)<>(B2<=$F$1), IF(C2<=$F$1, "Liability missing: cost belongs to 2025", "Booked too early: delivery in 2026"), TRUE, "Correct period")

=IFS(C2=""; "Delivery date missing"; (C2<=$F$1)<>(B2<=$F$1); IF(C2<=$F$1; "Liability missing: cost belongs to 2025"; "Booked too early: delivery in 2026"); TRUE; "Correct period")

In the journal entry testing at Veldhuis Installatie BV you rank seven manual entries by risk:

• Critical: an entry without approval, whatever its size
• High: posted on a weekend and at or above performance materiality (G2)
• Medium: posted on a weekend or at or above performance materiality, but not both
• Low: everything else

You want a single formula in E2 that classifies all seven entries and whose rules the reviewer can read.

Formula in E2

=LET(
  amount, B2:B8,
  weekend, C2:C8,
  approved, D2:D8,
  pm, $G$2,
  IFS(
    NOT(approved), "Critical",
    weekend * (amount >= pm) > 0, "High",
    weekend + (amount >= pm) > 0, "Medium",
    TRUE, "Low"
  )
)

=LET(
  amount; B2:B8;
  weekend; C2:C8;
  approved; D2:D8;
  pm; $G$2;
  IFS(
    NOT(approved); "Critical";
    weekend * (amount >= pm) > 0; "High";
    weekend + (amount >= pm) > 0; "Medium";
    TRUE; "Low"
  )
)

Result: High / Low / Medium / Medium / Critical / Medium / CriticalHigh / Low / Medium / Medium / Critical / Medium / Critical

Watch out: Swap conditions 2 and 3 and JE-0412 (weekend and above materiality) becomes Medium instead of High without any error, because condition 3 is also TRUE for it. The most specific rule must come before the rule that includes it.

=XLOOKUP(B2:B8, $I$2:$I$4, $J$2:$J$4, "No class", -1)

=XLOOKUP(B2:B8; $I$2:$I$4; $J$2:$J$4; "No class"; -1)