The perils of pivot tables

This morning saw the kneading of the 550th batch of bread in the nine years I have been doing it, that is to say since early 2011. Nothing unusual about it, beyond the dough being a little damper than usual and there being a marked change of texture (for the better) during the short pause in kneading for the first weighing. It also lost about half an ounce of its 5lbs 9.5oz by the time of the second weighing, presumably evaporation of that much water.

But I was prompted to try a pivot table, a feature of Microsoft Excel last noticed at reference 2, selecting for the purpose the 'weight of dough' field, with the results snapped left, legible if you click to enlarge.

The first oddity is the length of the resultant table, the result of the weight field actually being a text field, mainly expressed in pounds and ounces - for which see reference 1. Excel, quite properly, has sorted this text field alphabetically for the purposes of presentation of the pivot table - but a sort which does not present ascending weight.

Which confuses the chart on the right, although the two clusters around 4lbs and 5lbs are fair enough, with today's batch in the middle of the latter cluster.

The second oddity is the number of records found, 539, 11 short of the actual number. This looks likely to be the result of Excel dropping those rows for which there is no weight recorded, not even a bit of text saying not available, this despite it being pedantic about including '(blank)' as the last row in the table.

I sometimes toy with the idea of writing a bit of code to convert this text field to a numeric field with grams, but somehow never get around to it.

In the meantime, let it all stand as a warning, following that somewhere in reference 2, about the need to do some system analysis and design before embarking on data collection. Which in this case, might have resulted in the existing weight of dough column being rendered in numeric grams and in an additional weight of dough column, rounded to the nearest 10 grams, for the purpose of pivot tables in due course.

Reference 1: https://psmv4.blogspot.com/2020/02/house-of-lords.html.

Reference 2: https://psmv4.blogspot.com/2020/02/a-sort-of-pivot-table.html.