Ravindra BagaleCourses & study guides Track your progress
Hard challenge

Hard: Circular refs, dates & merges

By · Free ·

EasyMediumHard

Freight budget cells point at each other (circular → Err:522 in LibreOffice). Shipment dates are mixed text/ISO, a title merge invites bad sorts, Open charges are typed, and Needs_review uses AND when OR is required.

About this data: Made-up freight log for Ravi Iyer (Chennai logistics) and Sara Lee (NY clinic samples). Mixed INR/USD labels in notes only. Practice data — not real shipments.
Download broken workbook Download fixed (after you try)

Why → What → How

Why: Circular refs freeze planning models. Text dates sort wrong. Merged headers selected with data scramble rows. AND vs OR quietly flips risk flags.

What: Break B5/B6 circular pair; store real Date values; unmerge title away from columns; replace hard-coded open total with SUMIF; fix review flag to OR.

How:

  1. Open the freight sheet. Note Spent_so_far / Remaining show Err:522 (circular).
  2. Set Spent to =SUM of Charge_INR (or open+closed as designed) and Remaining to =Budget-Spent — one direction only.
  3. Convert text dates like 15-10-2026 into real dates (DATEVALUE / re-enter / Text to Columns).
  4. Unmerge any title that sits across data columns; keep a title above the table instead.
  5. Replace typed Open charges with =SUMIF(Status,"Open",Charge).
  6. Needs_review: flag when Status is Open or Kg>100 — use OR, not AND.

Ravindra Bagale’s Tip

If Excel warns about circular references, list every formula that mentions the other cell. Keep money flowing one way: Budget → Spent → Remaining.

Bugs explained (with mini examples)

Bug 1 — Circular reference

B5 (Spent) = Budget - Remaining B6 (Remaining) = Budget - Spent → Err:522 / circular warning Fix: Spent = SUM(charges); Remaining = Budget - Spent

Bug 2 — Dates as text

"15-10-2026" vs 2026-10-03 Text dates sort as text; month/day can swap by locale Fix: real Date serials, one format

Bug 3 — Merged cells vs sort

Merged title across A8:G8 selected with data → sort refuses or scrambles Fix: title in one row above; table unmerged

Bug 4 — Hard-coded SUMIF area

Typed Open total 50000 Real Open: 8400+15000+31000 = 54400 Fix: =SUMIF(StatusRange,"Open",ChargeRange)

Bug 5 — IF with AND instead of OR

Want Review if Open OR Kg>100 Broken AND misses Open rows with Kg≤100 Fix: =IF(OR(Status="Open",Kg>100),"Review","OK")

Practice tasks (12)

  1. Task 1. Name the two cells that cause the circular error.
    Hint: Spent and Remaining.
  2. Task 2. Write a non-circular Remaining formula.
    Hint: =Budget-Spent
  3. Task 3. Convert all ShipmentDate values to real dates.
    Hint: One format, e.g. YYYY-MM-DD.
  4. Task 4. Unmerge the title; place it above the header row.
    Hint: Do not merge into data columns.
  5. Task 5. Replace Open charges with SUMIF.
    Hint: Criteria "Open".
  6. Task 6. What is the correct Open total?
    Hint: 54400
  7. Task 7. Fix Needs_review for Omar (Open, 90 kg).
    Hint: OR → Review
  8. Task 8. Should Sara (Closed, 40 kg) be Review?
    Hint: OK
  9. Task 9. Sort by ShipmentDate oldest first after fixing dates.
    Hint: Ravi should not jump wrongly.
  10. Task 10. Explain Err:522 in one plain sentence.
    Hint: Two formulas depend on each other forever.
  11. Task 11. Add a new Open shipment 5000 ₹ and confirm SUMIF updates.
    Hint: Hard-coded 50000 would not.
  12. Task 12. Bonus: conditional format Review rows in orange.
    Hint: Home → Conditional Formatting.

In-browser formula check

Type the fixed formula you would put in the named cell (Excel or LibreOffice style). We only check the formula text — open the workbook for full recalculation.

Step-by-step solution (try first)
  1. Spent_so_far = SUM of Charge_INR (80600); Remaining = 100000 − 80600 = 19400.
  2. Normalize dates to real date values (2026-10-02 … 2026-10-21).
  3. Title not merged into header/data.
  4. Open charges = 54400 via SUMIF.
  5. Needs_review uses OR: Open rows and heavy Kg get Review.

LibreOffice verified: broken Err:522; fixed Spent 80600, Remaining 19400, Open 54400, OR flags corrected.

Quick quiz

Q1. A circular reference means:
Q2. Merged cells in a data table are risky because:
Q3. SUMIF is better than a typed open-total because:
Q4. Flag when Status is Open OR Kg>100 uses:
Q5. Text dates often fail when you: