Summarize and check

Report a total alongside the count of rows it covers, keep a blank cell and a recorded zero as two different answers, and add a reconciliation check while auditing row coverage separately.

A total on its own is not a fact. "Ninety learners attended" is only meaningful once you also know it covers six sessions out of eight, and that the other two are missing rather than empty.

The gap matters because the two obvious ways of handling a missing value point in opposite directions. Treat it as zero and the average falls, the site looks worse than it is, and somebody may cut a programme on the strength of it. Leave it out and the total understates the real figure, which may cost the programme its funding. Neither is more honest than the other in the abstract. What is dishonest is publishing a number without saying which one you did.

The habit that fixes this is small: never report a total without reporting how many rows it came from, and never let a range end where the data happened to end on the day you wrote it.

Six functions do most summarising, and the useful thing about them is what they disagree about.

SUM adds the numbers in a range. Text is skipped, blanks are skipped, and no warning is given for either.

AVERAGE divides the sum by the count of numbers, not by the number of cells. Over six numbers and two blanks it divides by six. That is often what you want and occasionally very much not.

MIN and MAX report the smallest and largest numbers present. A recorded zero will be the minimum; a missing value will not be, because it is not a number at all.

COUNT counts numbers. COUNTA counts cells that hold anything — a number, a word, a formula's result, or a single typed space. COUNTBLANK counts cells holding nothing at all.

Their disagreements are the diagnostic. If COUNT and COUNTA are equal, every populated cell in the range is a number. If COUNTA is larger, some populated cells hold something that will never be added up. If COUNT plus COUNTBLANK is less than the number of rows, at least one cell holds a nonempty nonnumeric value — for example a space, a dash or "n/a".

That last case is worth dwelling on. A cell containing a single space looks exactly like an empty cell on screen. COUNTBLANK does not count it. COUNTA does. There is no visual difference at all, and the only way to find out is to ask.

Blank, zero and missing are three states, not two.

• Zero is a measurement. The session ran and nobody came. It belongs in the total, it lowers the average, and it is the correct minimum.
• Blank is the absence of an entry. It may mean "not measured", or it may mean "not applicable", and the sheet cannot tell you which.
• Never recorded is a fact about the process, not about the session, and it is the one that deserves a note in a column of its own.

Filling a blank with zero converts a fact about your records into a claim about the world. Sometimes that is the right call — but it is a decision, it should be written down next to the number, and it should never happen by accident because a formula treated an empty cell as nought.

Finally, a reconciliation is a cell whose only job is to be zero. Add the same rows a second way — in two halves, from a different column, against a control figure someone else produced — and subtract. A nonzero difference reveals a net mismatch between the specified sums. Zero does not prove complete coverage: omitting a blank, recorded zero or typed-space row can leave the sum unchanged, and two ranges can adjust together after an insertion. Check the expected session IDs, counts and range endpoints independently.

Eight sessions, three of them awkward:

     A        D          E
1  Session  Attended   Note
5  CR-104              Register never returned
8  CR-107      0       Session ran, nobody came
9  CR-108   (space)    Someone typed a space here

Six summary cells over D2:D9 tell the whole story:

=SUM(D2:D9)        90
=COUNT(D2:D9)       6
=COUNTA(D2:D9)      7
=COUNTBLANK(D2:D9)  1
=MIN(D2:D9)         0
=AVERAGE(D2:D9)    15

Read it as a sentence. Ninety learners across six recorded sessions, averaging fifteen. Seven of the eight rows hold something but only six hold a number, so one row holds a non-number — that is the space in D9. One row holds nothing at all — that is the missing register in D5. The minimum is zero because CR-107's zero is real.

Now notice what a reader who only saw SUM would take away: ninety, over eight sessions, averaging 11.25. That figure is not the average of measured attendances. It would require an explicit decision to treat the two unrecorded attendances as zero.

The worked example reports the current eight rows. Now test two different corrections to D9. In the first, someone verifies CR-108 ran with nobody attending and replaces the typed space with the numeric zero. In the second, the space is cleared to a true blank because attendance is still unknown. Leave every other cell as it is.

Predict COUNT, COUNTA, COUNTBLANK and AVERAGE over D2:D9 in each case. Which case adds a measured session to the average?

A summary written last month:

=SUM(D2:D7)

It was correct on the day it was written, when there were six rows. Two sessions have been added since. In these exact rows, D8 is a recorded zero and D9 is a space, so the old range still totals 90 by coincidence. It is incomplete even though its current amount agrees.

Extending the range fixes today:

=SUM(D2:D9)

A second sum can check net agreement over the ranges it reads:

=H2-(SUM(D2:D5)+SUM(D6:D9))

That cell reads zero for these specified ranges. A nonzero result needs investigation, but zero is only net agreement; it does not establish that every session was counted once. In particular, a blank or zero-valued row can be omitted without changing the amount.

Rows added below row 9 can fall outside all three ranges. An insertion inside them can also leave the sums equal if formulas adjust together. Audit the eight expected session IDs and current range endpoints, and keep the row count beside the total; amount reconciliation does not replace either check.

A site manager reports that average attendance at Ridge has "collapsed to 7". The sheet says so: seven sessions this term, 49 learners recorded in total, 49 divided by 7 is 7. The manager who runs Ridge says the sessions have been full.

Both are telling the truth about what they can see. Work out why.

1. Count before you interpret. Put COUNT, COUNTA and COUNTBLANK over the same range. If they disagree, the average is being computed over a different set of rows than anybody assumes.
2. Find the missing rows and read the note column. Two Ridge registers were never returned. There is no attendance figure for them, because none was ever taken.
3. Ask which formula produced the 7. If it divides the total by the count of sessions rather than the count of numbers, the two missing registers are being counted as zero-attendance sessions.
4. Ask whether that is true. It is not. Nobody counted those rooms. The sessions may have been full.
5. Decide, and write the decision down. The 49 learners were counted across five sessions, not seven, so the average over sessions that were actually counted is 49 divided by 5, or 9.8. Either report that with its coverage — "average 9.8 across 5 of 7 sessions, 2 registers outstanding" — or report the zero-filled 7 and label it as the zero-filled figure. What you may not do is report 7 as "average attendance at Ridge".

The general shape: a value that surprises somebody who was there is usually a coverage problem, not a measurement problem. Check what the denominator is made of before you believe the fraction.

The eight rows from the worked example are in the grid. Build the summary block in column H, finish the reconciliation, and answer the judgement question at the bottom.

The chart under the grid draws the attendance column. Watch what happens to the two rows with no number: the chart cannot show the difference between "nobody came" and "nobody counted", but the data table under it can.

Find a summary somebody else produced — a report at work, a figure in a newsletter, a total on a dashboard — and answer three questions about it.

1. How many records is this number made of? If the report does not say, that is your finding.
2. What happened to the records that were missing or incomplete? Were they excluded, zero-filled, or estimated? Each choice moves the number in a different direction.
3. If one record were added tomorrow, would this number update, or would somebody have to remember to change it?

Write your answers in three sentences. If you cannot answer question 1 from the report itself, you have learned the most useful thing this lesson has to offer, which is how often that question has no answer.

Sign in to track your progress on this exercise.

This module is finished when you can do all of these.

• Name the three functions whose disagreement reveals that a column contains something other than numbers, and say what each disagreement means.
• Explain why AVERAGE over a range with blanks is not the same as the total divided by the number of rows, and say when each is the right figure.
• Describe the difference between a recorded zero and a missing value, and give one decision each would push in a different direction.
• Write a reconciliation cell for a column of your own, and say what change would make it stop reading zero.
• Say what a reconciliation does not catch.

Sign in to track your progress on this exercise.

A total is reportable when it states the numeric count and expected session count, distinguishes blank from measured zero, and pairs its amount check with a separate audit of the source rows and range endpoints.

That completes the first module. You can now lay out a worksheet someone else can check, write formulas that survive being copied, and summarise a column without overstating what it covers.

Next comes the data itself. Module 2 starts with a CSV export whose identifiers lose their leading zeros, whose dates could be read two ways, and whose duplicate rows are not all duplicates.