Easy: Wrong absolute references
Commission column multiplies each sale by a rate cell — but the formula was filled down without locking $E$5. SUM also stops too early, and the manager typed a hard-coded grand total.
Commission column प्रत्येक sale ला rate cell ने गुणते — पण fill down करताना $E$5 lock केले नाही. SUM लवकर थांबते, आणि manager ने hard-coded grand total टाइप केला.
Commission column हर sale को rate cell से गुणा करता है — पर fill down में $E$5 lock नहीं हुआ। SUM जल्दी रुकती है, और manager ने hard-coded grand total टाइप किया।
Why → What → How
Why: One unlocked rate cell silently gives 0, #VALUE!, or huge nonsense commissions when copied down. Managers then “fix” totals by typing numbers — worse.
What: Three bugs in Commission sheet: relative rate reference, SUM range D8:D14 missing Emma/Ravi rows, hard-coded 90000 instead of a formula.
How:
- Download the broken file and open sheet Commission.
- Look at E8 — it uses E5. Fill-down made E9 use E6 (empty), E10 use E7 (label text), and so on.
- Change the rate part to
$E$5(orE$5if you only lock the row) and fill down. - Widen Total sales from
=SUM(D8:D14)to include all sale rows (through D16). - Replace the typed 90000 with a formula that equals the real total.
- Confirm commissions are all 5% and both totals match.
Ravindra Bagale’s Tip
Press F4 (or Cmd+T on Mac in some builds) while the cursor is on E5 inside the formula to cycle $ locks.
Ravindra Bagale’s Tip – मराठी
Formula मध्ये E5 वर cursor ठेवून F4 दाबा — $ lock cycle होते.
Ravindra Bagale’s Tip – हिंदी
Formula में E5 पर cursor रखकर F4 दबाओ — $ lock cycle होता है.
Bugs explained (with mini examples)
Bug 1 — Relative rate reference
Broken idea: =D8*E5 copied to next row becomes =D9*E6. E6 is empty → 0 commission.
Bug 2 — SUM misses new rows
Data grew to row 16 but the total still sums D8:D14. Emma and Ravi are invisible to the total.
Bug 3 — Hard-coded total
Someone typed 90000. It never updates when sales change. Use a formula (same SUM, or =D18 if that cell already holds the SUM).
Practice tasks (12)
- Task 1. In E8, write a commission formula that always reads the 5% in E5.Hint: Use $E$5.
- Task 2. Fill the fixed formula down through Ravi’s row.Hint: Double-click the fill handle after locking $E$5.
- Task 3. Explain why Omar’s broken commission showed 0.Hint: E9 pointed at empty E6.
- Task 4. Explain one #VALUE! commission in the broken file.Hint: A shifted ref hit the text label near the rate.
- Task 5. Fix Total sales so Emma (15600) and Ravi (13300) are included.Hint: Extend to D16.
- Task 6. Replace the hard-coded grand total with a formula.Hint: Do not type 122900 by hand.
- Task 7. What is Priya’s commission on 12000 at 5%?Hint: 600.
- Task 8. If the rate in E5 changes to 6%, should every commission update?Hint: Yes — only if you used $E$5.
- Task 9. Write one sentence you would tell a junior about hard-coded totals.Hint: Typed totals lie when data changes.
- Task 10. In LibreOffice or Excel, sort by City after fixing — do commissions stay correct?Hint: Yes if rate is absolute.
- Task 11. Add a new sale row 17 for Neha, 10000 ₹ — update SUM.Hint: Include D17 or convert the range to a Table.
- Task 12. Bonus: format Commission column as ₹ currency.Hint: Home → Number → Currency / Accounting.
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)
- E8:
=D8*$E$5→ fill to E16. - Total sales cell:
=SUM(D8:D16). - Grand total: same SUM or
=the Total sales cell. - Expected total sales: 122900. Sample commissions: 600, 925, 475, 710, 550, 1005, 435, 780, 665.
Verified in LibreOffice Calc: broken showed 0 / #VALUE! / huge commissions and SUM 94000; fixed commissions and 122900.
Quick quiz