Updated rules do not change E
Use the current policy cells instead of pasting the original fixed branch rates into the maintained result.
Refactor a nested IF policy into a maintained rule table
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Build D2:F9 using three separate column seeds. D retains the frozen nested-IF policy: negative quantities are Review quantity; nonnegative quantities below 5 are Below minimum; the inclusive minima 5,10,25,50 select flat rates 12,10,8,6. E reads the current four-row J2:K5 policy using the largest minimum at or below each quantity, regardless of table order, with the same negative/below-minimum labels. F returns Same when both typed results agree and Changed otherwise. Fill D, E and F separately; every result needs its own copied formula. Quantities and unique nonnegative minima are whole numbers within ±1,000,000 (minima cannot be negative); current flat rates are nonnegative whole-cent numbers up to 1,000,000. These are flat rates, not progressive totals.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: E2, D2, F2, D3, E3, F3, D4, E4, F4, D5, E5, F5, D6, E6, F6, D7, E7, F7, D8, E8, F8, D9, E9, F9. Browser calculations may differ from your Excel version.
Copy one formula and compare
Each report column has its own method. Copy within that column, inspect its applied formulas and fill each of the three seeds separately. Current results below compare the frozen baseline with the editable policy.
A1 moves both parts; $A$1 locks both; $A1 locks the column; A$1 locks the row. Keep current-policy references fixed while quantity and comparison rows move down. A rightward copy between method columns does not refactor the policy.
Current maintained policy
The frozen IF baseline keeps 5 → 12, 10 → 10, 25 → 8, 50 → 6. The current rules below can change independently. These are flat rates, not percentages or progressive charges.
| Current minimum | Current flat rate |
|---|---|
| 5 | 12 |
| 10 | 10 |
| 25 | 8 |
| 50 | 6 |
Compare each request
| Quantity | Frozen IF | Current table | Comparison |
|---|---|---|---|
| -1 | — | — | — |
| 0 | — | — | — |
| 5 | — | — | — |
| 9 | — | — | — |
| 10 | — | — | — |
| 24 | — | — | — |
| 25 | — | — | — |
| 50 | — | — | — |
Inspect all written formulas
| Cell | Applied formula | Calculated value |
|---|---|---|
| E2 | No formula | — |
| D2 | No formula | — |
| F2 | No formula | — |
| D3 | No formula | — |
| E3 | No formula | — |
| F3 | No formula | — |
| D4 | No formula | — |
| E4 | No formula | — |
| F4 | No formula | — |
| D5 | No formula | — |
| E5 | No formula | — |
| F5 | No formula | — |
| D6 | No formula | — |
| E6 | No formula | — |
| F6 | No formula | — |
| D7 | No formula | — |
| E7 | No formula | — |
| F7 | No formula | — |
| D8 | No formula | — |
| E8 | No formula | — |
| F8 | No formula | — |
| D9 | No formula | — |
| E9 | No formula | — |
| F9 | No formula | — |
Change a source value
Apply a whole-number quantity or unique nonnegative minimum, or a nonnegative whole-cent flat rate. Changes affect the current table result while the original IF policy stays fixed.
Applied value:
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Compare a frozen multi-branch policy with a current editable lower-bound table, then identify every request whose selected flat rate changes.
THE QUICK ANSWER01
Keep the original IF decision as a baseline, then move maintained policy data into J2:K5. XLOOKUP selects exact or next-smaller minima with a linear search; IF still handles the separate negative-input guard.
ƒx
=IF(B2<0,"Review quantity",XLOOKUP(B2,$J$2:$J$5,$K$2:$K$5,"Below minimum",-1,1))For the example data
D2 Review quantityE2 Review quantityF2 SameD3 Below minimumE3 Below minimumF3 SameD4 12E4 12F4 SameD5 12E5 12F5 SameD6 10E6 10F6 SameD7 10E7 10F7 SameD8 8E8 8F8 SameD9 6E9 6F9 Same
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
A frozen baseline is intentional
The demo policy initially agrees with the original IF rules. In the changed-rate population, quantity 5 keeps the original rate 12 in D but selects current rate 12.5 in E, so F reports Changed. This comparison distinguishes a formula rewrite from a business-rule revision.
IF and a table serve different roles
A cell-linked IF can also update policy values; IF remains useful for guards and unrelated conditions. A maintained table separates the band records from the selection expression and makes row reordering explicit. This task does not claim that every branch should be replaced with lookup.
Inclusive lower bounds and missing rules
At a current minimum the new band applies immediately. Below every current minimum, the result is a text label rather than a zero rate. Negative quantities follow the separate review rule in both representations.
Three seeds, three column fills
D, E and F have different jobs. Fill each column from its own top formula. A rightward copy of the old IF is not a refactoring of the policy, and filling one seed over D2:F9 would overwrite the other methods.
Read the full step-by-step walkthrough
- Make the frozen decision explicit
Apply the original five-IF seed to D2 and fill D2:D9. The literal baseline rules stay fixed during policy edits, so this column records the original decision rather than today's rule table.
- Separate rules from the lookup expression
J2:K5 contains four current minimum/rate pairs. Every row stays paired, and its physical position does not determine priority.
- Retain the guard clause
A negative quantity returns Review quantity before lookup. A nonnegative quantity below every current minimum returns Below minimum; a selected zero rate remains numeric zero.
- Select the largest qualifying minimum
Use exact-or-next-smaller match mode -1 and linear search mode 1. Do not use sorted binary search on the reordered rule populations. Apply the seed to E2 and fill E2:E9 only.
- Compare each complete typed result
Apply IF(D2=E2,"Same","Changed") to F2, then fill F2:F9. Compare both outputs per request instead of inferring agreement from a grand total.
- Change one maintained rule
Apply a new current rate or unique minimum through the source editor. The baseline remains unchanged; the maintained lookup and the Same/Changed rows recalculate from the actual policy table.
Things to watch
Reordering changes the selected rate
Use a linear exact-or-next-smaller search, not a sorted binary search on unsorted rules.
Below minimum looks like a free rate
Below minimum is text. A selected rate of zero is numeric and belongs to a valid band.
Filling one method removes the others
This report fills one column at a time. Apply and fill D2, then E2, then F2.
About this example
Original finite policy comparisons, typed current/retained results and formula/frozen workbooks; native-client certification remains separate.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.