Ravindra BagaleCourses & study guides Track your progress
Hard challenge

Hard: Conditional formatting out of sync with IF

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

Flag uses ≥8 but red CF uses >5, so T2 (6 hours) looks bad while Flag says OK. Fix CF to ≥8.

About this data: Made-up SLA tickets for Riya Sen (Kolkata) and Marco Silva (Lisbon). Practice only.
Download broken workbook Download fixed (after you try)

Why → What → How

Why: Dashboards lie when CF thresholds drift from the business IF rule.

What: Five tickets; broken CF >5; fixed CF ≥8.

How:

  1. Open Flags.
  2. Compare Flag to CF rule.
  3. Edit CF to ≥8.
  4. Confirm T2 not red.

Ravindra Bagale’s Tip

Store the threshold in a cell; point both IF and CF at it.

Bugs explained (with mini examples)

Bug 1 — Drifted CF

>5 vs ≥8.

Bug 2 — Two sources of truth

Centralize cutoff.

Practice tasks (12)

  1. Task 1. T2 hours?
    Hint: 6.
  2. Task 2. Flag for T2?
    Hint: OK.
  3. Task 3. Fix CF.
    Hint: ≥8.
  4. Task 4. T5 Over+red?
    Hint: 12h.
  5. Task 5. Interview.
    Hint: CF disagreed with IF.
  6. Task 6. Manage Rules.
    Hint: Home → CF.
  7. Task 7. Threshold cell.
    Hint: $E$1.
  8. Task 8. Test T3=9.
    Hint: Over.
  9. Task 9. Clear and re-add.
    Hint: OK.
  10. Task 10. Bonus formula CF.
    Hint: =$B5>=$E$1
  11. Task 11. Document.
    Hint: A11.
  12. Task 12. Save.
    Hint: Yes.

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. Align CF with ≥8.
  2. T2 OK visually; T3/T5 Over.

Quick quiz

Q1. IF ≥8 and CF >5:
Q2. T2=6 with ≥8 is:
Q3. Centralize thresholds in:
Q4. CF manage rules under:
Q5. Business rule here: