Every TRUE condition is assumed to be exclusive
Independent urgency and Review can both be true. The selected branch order decides which label is returned.
Resolve overlapping request rules with explicit priority
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Fill G2:G9, H2:H9, I2:I9 and J2:J9 separately from their own top seeds. B is whole age hours 0..10000, C whole impact 0..10, D repeated and E acknowledged are actual Boolean values, F is the exact text Open or Closed. M2 and M3 are whole age/impact thresholds with the same bounds; M4 is 1 Escalate-first or 2 Review-first. Urgent means NOT acknowledged AND (age >= M2 OR impact >= M3). Review means repeated OR (acknowledged AND impact >= M3). Closed always overrides all other rules. G reports whether both urgent and review match an open row. H uses genuine nested IF and I first-match IFS with an explicit Routine fallback. For open rows use the selected priority to choose Escalate or Review when both match, otherwise the sole matching rule or Routine. J compares the two written routes as a Boolean. Apply actual AND, OR and NOT in both routes. No rule inference or real-world service commitment is implied.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G2, G3, G4, G5, G6, G7, G8, G9, H2, H3, H4, H5, H6, H7, H8, H9, I2, I3, I4, I5, I6, I7, I8, I9, J2, J3, J4, J5, J6, J7, J8, J9. Browser calculations may differ from your Excel version.
Inspect current overlap, routes and reconciliation
| Cell | Current value | Applied formula |
|---|---|---|
| G2 | — | No formula |
| G3 | — | No formula |
| G4 | — | No formula |
| G5 | — | No formula |
| G6 | — | No formula |
| G7 | — | No formula |
| G8 | — | No formula |
| G9 | — | No formula |
| H2 | — | No formula |
| H3 | — | No formula |
| H4 | — | No formula |
| H5 | — | No formula |
| H6 | — | No formula |
| H7 | — | No formula |
| H8 | — | No formula |
| H9 | — | No formula |
| I2 | — | No formula |
| I3 | — | No formula |
| I4 | — | No formula |
| I5 | — | No formula |
| I6 | — | No formula |
| I7 | — | No formula |
| I8 | — | No formula |
| I9 | — | No formula |
| J2 | — | No formula |
| J3 | — | No formula |
| J4 | — | No formula |
| J5 | — | No formula |
| J6 | — | No formula |
| J7 | — | No formula |
| J8 | — | No formula |
| J9 | — | No formula |
Change an actual source value
B: whole age hours 0–10000; C: whole impact 0–10. D/E must be actual Boolean flags, F the exact text Open or Closed. M2/M3 set inclusive thresholds; M4 is numeric 1 Escalate-first or 2 Review-first. Closed overrides both policies. The four answer columns fill separately from G2, H2, I2 and J2.
This draft matches the current cell.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Compare nested IF and first-match IFS on the same requests. Combine AND, OR and NOT, expose overlapping rules and change the priority in a real worksheet cell.
THE QUICK ANSWER01
A true condition is not a unique decision. When two independent rules can match, the first applicable branch encodes an explicit policy.
ƒx
=IF(F2="Closed","Closed",IF($M$4=1,IF(AND(NOT(E2),OR(B2>=$M$2,C2>=$M$3)),"Escalate",IF(OR(D2,AND(E2,C2>=$M$3)),"Review","Routine")),IF(OR(D2,AND(E2,C2>=$M$3)),"Review",IF(AND(NOT(E2),OR(B2>=$M$2,C2>=$M$3)),"Escalate","Routine"))))For the example data
G2 falseH2 EscalateI2 EscalateJ2 trueG3 falseH3 EscalateI3 EscalateJ3 trueG4 trueH4 EscalateI4 EscalateJ4 trueG5 falseH5 ReviewI5 ReviewJ5 trueG6 falseH6 ReviewI6 ReviewJ6 trueG7 falseH7 RoutineI7 RoutineJ7 trueG8 falseH8 ClosedI8 ClosedJ8 trueG9 falseH9 RoutineI9 RoutineJ9 true
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Inclusive boundaries and acknowledgement are separate
Equality meets an age or impact threshold. Acknowledgement suppresses urgency even when both thresholds are exceeded; it can still qualify for Review through high impact.
Order resolves overlap
IF and IFS do not make a priority policy correct by themselves. The same overlapping conditions produce different valid outputs under two explicitly different policies. Closed is checked before both.
Copied formulas must remain source-dependent
Each of the four report columns has its own method and fixed policy references. Independent checks use the current source, eight retained sources and three changed-source probes. TRUE reconciliation alone does not prove either route is correct.
Read the full step-by-step walkthrough
- Separate inputs, rules and the final decision
Read B:F as stored values, not truthy text. M2:M3 are inclusive thresholds and M4 is the priority mode. Before evaluating either open-row rule, Closed must produce Closed in both routes.
- Build the urgency rule from AND, OR and NOT
An unacknowledged request is urgent when either its age or impact reaches the threshold. NOT applies only to acknowledgement. AND requires that guard as well as the OR result; changing AND to OR would escalate acknowledged requests.
- Keep Review as an independent condition
Repeated requests qualify for Review. Acknowledged high-impact requests also qualify. This rule does not become false merely because urgency is true, so a repeated unacknowledged urgent request can match both rules.
- Expose overlap before choosing a route
G is a Boolean diagnostic: only open rows with both conditions true have overlap. A closed row stays FALSE even if its age, impact and repeated flag would meet both open-row rules.
- Make nested branch priority explicit
H checks Closed first, then M4. Mode 1 checks urgency before Review; mode 2 reverses those two checks. Both policies keep the same Boolean conditions and Routine fallback. Fill H from its own seed, without copying the G method into it.
- Compare first-match IFS with the same policy
IFS returns the first TRUE branch. The mode-1 urgency test precedes Review; the later urgency branch handles remaining mode-2 requests. The final TRUE supplies Routine when nothing else matches. J must reconcile H and I without replacing either route with a fixed label.
- Change priority and observe the overlapping rows
Switch to Your turn and change M4 from 1 to 2. Only open rows that match both rules change route; closed, single-rule and Routine rows retain theirs. Then change a threshold or Boolean input and freshly check the complete report.
Things to watch
A closed high-impact request is routed again
Closed is an overriding state and must be checked before either open-row rule.
Acknowledgement is ignored or text is coerced
Use NOT on the actual Boolean E value. Text TRUE, numeric 1 and a stored TRUE Boolean are different inputs.
Matching route columns are treated as proof
Two equally wrong routes can agree. The independent source rules check each route, not only J.
About this example
Original bounded browser practice. Native client calculation, regional presentation and edit/save remain separately unverified.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.