Skip to main content
SheetTutor.
SheetTutorLearn Excel by playBack to task

Refactor a nested IF policy into a maintained rule table

Your goal: Compare a frozen multi-branch policy with a current editable lower-bound table, then identify every request whose selected flat rate changes.

Full 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.

Frozen and current policies initially agreeKeep each minimum paired with its flat rate0 cells selected
B contains request quantities. J:K contains current paired minima/rates; the frozen baseline stays unchanged.Scroll the worksheet vertically for later source rows and horizontally for later columns.
Click or drag source cells; selected cells change color Drag from selected cells to deselect.
#
1
2
3
4
5
6
7
8
9
Step 1 of 11

Keep each minimum paired with its flat rate

Select the source cells in the worksheet, then complete the choices below.

Rules for this step

Select the current J2:K5 rule table. Physical row order does not define priority; select the largest minimum at or below the request quantity.

0 cells selected