Find the total row that doubles a spreadsheet sum

short · 30 min · Objective 1.4

Task

Build a regional summary the way a spreadsheet report often looks -- with a total row typed underneath the data -- and show what happens when someone sums the whole column. It is one of the quietest spreadsheet failures there is.

Steps

  1. Count customers by region in data/customers.csv and write lab/sheet/report.csv with header region,customers, one row per region, then a final row TOTAL with the sum.
  2. Sum the customers column over every row of the report, as selecting the whole column would, and record it in lab/sheet/sums.txt as whole column: N.
  3. Sum it again excluding the TOTAL row and record it as regions only: N.

Verify

These checks run in a POSIX shell with Python 3: Terminal on macOS or Linux, and on Windows Git Bash (it comes with Git for Windows) or WSL. On Windows, python3 is often a Microsoft Store placeholder that prints “Python was not found”; run alias python3=py in Git Bash first.

grep -E '^(whole column|regions only): ' lab/sheet/sums.txt
awk -F': ' '/^whole/ {w=$2} /^regions/ {r=$2} END {print w/r" times the true total"}' lab/sheet/sums.txt
grep -c '^TOTAL,' lab/sheet/report.csv

The whole-column sum is 600 and the true total 300: exactly 2 times, because the total row was counted along with the rows it totals. Reports meant for people should keep totals out of the data range, or data meant for analysis should never contain them.

This is an independent study companion for CompTIA Data+ DA0-002 and is not produced by or endorsed by CompTIA.