Ravindra BagaleCourses & study guides Track your progress
Medium challenge

Medium: Nested IF wrong band order

By · Free ·

Absolute refsNumbers as textLeading spacesFilter left onChart range% vs decimalDuplicate headerROUND vs displayFreeze panesPaste valuesVLOOKUP & textVLOOKUP col indexHLOOKUP TRUESUMIF spacesCOUNTIFS ORIF nest orderDate serialINDEX MATCHWildcard COUNTIFMixed currencyCircular & datesCircular interest3D sum renameNamed range staleCF vs IFPivot staleHard-coded NPVValidation movedPQ step orderTyped-over formulaSUM wrong anchorsFiscal year Apr–MarFind all bugsInterview oral

Bands: Lead≥12, Senior≥8, Mid≥3, else Junior. Broken IF tests ≥3 first — Divya (12) → Mid. Fixed high-to-low → Lead (LibreOffice verified).

About this data: Made-up tenure for Divya Rao (Vizag) and Mark Chen (Singapore). Practice only.
Download broken workbook Download fixed (after you try)

Why → What → How

Why: Nested IF short-circuits. Low thresholds first steal higher bands.

What: Five staff. Divya broken Mid → fixed Lead.

How:

  1. Open Staff.
  2. Read Divya band.
  3. Rewrite IF testing 12, then 8, then 3.
  4. Confirm Lead.

Ravindra Bagale’s Tip

Always nest from the highest cutoff down — or use a lookup table.

Bugs explained (with mini examples)

Bug 1 — Low test first

≥3 catches everyone including Lead.

Bug 2 — No edge-case tests

Check 2,3,7,8,11,12.

Practice tasks (12)

  1. Task 1. Broken Divya band?
    Hint: Mid.
  2. Task 2. Fixed?
    Hint: Lead.
  3. Task 3. Write correct IF.
    Hint: ≥12 Lead…
  4. Task 4. Mark (6)?
    Hint: Mid.
  5. Task 5. Ira (15)?
    Hint: Lead.
  6. Task 6. Interview sentence.
    Hint: “IF order wrong.”
  7. Task 7. Leela (2)?
    Hint: Junior.
  8. Task 8. Sam (9)?
    Hint: Senior.
  9. Task 9. Why lookup table helps.
    Hint: No nest order bugs.
  10. Task 10. Test boundary 8.
    Hint: Senior.
  11. Task 11. Bonus: IFS function if available.
    Hint: Same order rule.
  12. Task 12. Document bands.
    Hint: A11.

In-browser formula check

Type the fixed formula you would put in the named cell. We check formula text — open the workbook for full recalculation.

Step-by-step solution (try first)
  1. Nest high-to-low.
  2. Divya → Lead. LO: Mid → Lead.

Quick quiz

Q1. If you test ≥3 before ≥12, a 12-year staffer becomes:
Q2. Fixed Divya band:
Q3. Broken LO Divya:
Q4. Safe nest order:
Q5. Years for Lead in this file: