Hard: SUM with wrong absolute anchors
Total uses =SUM($B$5:$B$8) so Travel+Misc are ignored (15249). Fixed =SUM(B5:B10) → 18049 (LibreOffice verified).
SUM($B$5:$B$8) नवीन ओळी चुकवते → 15249. Fixed =SUM(B5:B10) → 18049.
SUM($B$5:$B$8) नई पंक्तियाँ छोड़ देता है → 15249। Fixed =SUM(B5:B10) → 18049।
Why → What → How
Why: Absolute both ends of a SUM look “safe” but freeze the range when the ledger grows.
What: Broken total 15249. Fixed 18049.
How:
- Open Ledger.
- Compare B12 to the Expected cell.
- Widen the SUM to B5:B10 (relative ends).
- Confirm 18049.
Ravindra Bagale’s Tip
Use absolute anchors only for the corner that must not move — or convert the block to a Table so formulas expand.
Ravindra Bagale’s Tip – मराठी
दोन्ही टोके $ करू नका — Table वापरा किंवा फक्त खरे स्थिर cell lock करा.
Ravindra Bagale’s Tip – हिंदी
दोनों सिरे $ मत करो — Table बनाओ या सिर्फ सच में स्थिर cell lock करो।
Bugs explained (with mini examples)
Bug 1 — Double absolute SUM
Bug 2 — Insert mid-block still stuck
Practice tasks (12)
- Task 1. What is broken B12?Hint: 15249.
- Task 2. Write the fixed formula.Hint: =SUM(B5:B10)
- Task 3. Expected total?Hint: 18049.
- Task 4. Which rows were missed?Hint: Travel and Misc.
- Task 5. Why did $…$ hurt here?Hint: Froze the range.
- Task 6. Interview sentence.Hint: SUM anchors were too absolute.
- Task 7. Change Misc to 700 — fixed updates?Hint: Yes.
- Task 8. Name the sheet.Hint: Ledger.
- Task 9. How many amount rows?Hint: 6.
- Task 10. Prefer Excel Table?Hint: Yes for growing lists.
- Task 11. Trace precedents on B12.Hint: Formulas tab.
- Task 12. Save fixed.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)
- Replace B12 with =SUM(B5:B10).
- LO: 15249 → 18049.
Quick quiz