Unit 02 of 02
Summary and Cleanup
Measuring which decisions produce the numbers that look at the whole table: how many rows remain and how many records disappear when the deduplication key changes, the three values that imputation and exclusion give for the same mean and the separation of an blank cell from a zero, a pivot table that returns two different winners when the breakdown changes, how many charts do not fit their scale and how many times larger the axis start makes a difference look, and how many of the decisions made in the course can be read back from the table.
Published Updated
Lessons
- 01 Removing Duplicate Records Five deduplication keys applied to the same 2,037-row sheet leave five different counts: 2,005, 1,400, 1,877, 1,965 and 1,877 again. When the key is the subscriber number alone, 637 rows are deleted and the total falls from 42,645.6 m³ to 29,828.2 m³, because most of the deleted rows are second-period readings rather than duplicates; the text trimming decision alone moves 88 rows. Keeping the last entry instead of the first under the same key leaves the row count fixed at 1,877 but pulls the totals 200.1 m³ apart. Once the operation is done, the deleted sheet gives back zero of four decisions, the annotated sheet two in two steps, and the traced sheet four in one step. 14 min read
- 02 Dealing with Missing Data In the reading column of the same 1,877-row sheet 116 cells are blank and 112 cells are zero, and 55 of those zeros are not real zero consumption but an unread visit; the 2,719.2 m³ that corresponds to the blank cells is inside the reported total. The mean of the same column comes out as 19.23, 18.04, 20.54 and 19.23 m³ again under four decisions: 2.49 m³ between the lowest and the highest, while imputation leaves the mean unmoved and raises the total from 33,866.8 to 36,097.6 m³. The blank-cell rate varies between regions from 2.0% to 17.3%, and when blanks are counted as zero the mean of one region falls by 3.94 m³. Once the operation is done, the imputed sheet gives back zero of four decisions, the annotated sheet one in two steps, and the traced sheet four in one step. 15 min read
- 03 Pivot Tables Five summary decisions applied to the same 1,761-row sheet make three different regions come out first: north with 10,905.8 m³ in total consumption, east with 22.80 m³ in the mean per row, central with 28.39 m³ in the mean per subscriber. When the region mean is produced from the cells of the cross tabulation the order holds, but the 3.35 m³ gap between south and west shrinks to 0.04 m³. When the same 20 m³ threshold is applied after the summary three regions pass, and when it is applied before the summary all five pass and the value of the east region rises from 22.80 to 29.03 m³. The pivot table gives two of its four decisions in the header row in one step, and never gives the position of the filter or the level of the calculated field. 15 min read
- 04 Basic Charts Four of ten chart suggestions can be drawn but do not fit the scale of the field or the summability of the measure: a line over a nominal axis, a line over an ordinal axis, a scatter over a field that is a label, and a pie from means. When the same five region means are drawn with two axis baselines, the actual ratio of 1.67 between east and west gives a bar length ratio of 1.63 when the axis starts at zero and 13.00 when it starts at 13 m³; the same difference looks 8.0 times larger and not a single reading changes. The chart reports to the sheet only the range it draws: of four decisions, one can be read back from the charted sheet, two from the annotated sheet and four from the traced sheet. 15 min read
- 05 Limits of Spreadsheets Scanning the 32 decisions made across the course's eleven lessons shows that, as commonly written, only 7 can be read back from the sheet: 6 in one step, 1 in two steps, while 25 appear in no cell. Writing down all of the decisions would take 32 cells, about 3.4 parts per thousand of a sheet with 9,385 data cells. As the sheet grows, a range-reading column becomes 626 times more expensive than a fixed-cost column and quadruples when the rows double; meanwhile a one-row range error shows up in two of four summaries on a 60-row sheet and in none of them on a 1,877-row sheet. Two formula texts fill 7,508 cells: that is the work the tool does cheaply. 13 min read