Chapter 04 · 7 min · Basic
Counting things correctly
Counting looks like the part of the job with no judgement in it. Ten rows are ten rows.
They are not. A column of customer ratings where some people did not rate, and one rated zero, supports several different counts and several different averages, all of them defensible — and the one you pick silently sets the answer. This chapter is that column, and the four decisions hiding in it.
The formulas are the ones you already know. The work is knowing which rows you are counting and being able to say why.
The grid
Ten support tickets in A1:D11 — ticket_id, agent, rating out of five, and whether it was resolved. Some tickets were never rated and their cells are genuinely empty. One customer rated the ticket 0. Work in column F.
Ten tickets, seven ratings
COUNTA on the id column says ten tickets. COUNT on the rating column says something smaller, because COUNT only counts numbers and an empty cell is not one.
That gap is not a data-quality problem to fix. It is a fact about the world: three customers were asked and did not answer. Nothing you type will recover their opinion, and filling those cells with a zero, or a three, or last month's average, invents data.
The gap is the first thing to measure and the first thing to report.
Ten tickets were logged. How many of them carry a number in the rating column?
Scroll to see all 6 columns →
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ticket_id | agent | rating | resolved | ||
| 2 | 4001 | Aisyah | 5 | yes | =COUNT(C2:C11) | |
| 3 | 4002 | Ben | yes | |||
| 4 | 4003 | Aisyah | 4 | yes | ||
| 5 | 4004 | Chong | 0 | no | ||
| 6 | 4005 | Ben | 3 | yes | ||
| 7 | 4006 | Chong | no | |||
| 8 | 4007 | Aisyah | 5 | yes | ||
| 9 | 4008 | Ben | 2 | yes | ||
| 10 | 4009 | Chong | 4 | yes | ||
| 11 | 4010 | Aisyah | yes |
Basic
The rest of this chapter is Basic
The Excel tutorial and Spreadsheet Fundamentals are free here, forever. This track is the paid half: what to actually do with a spreadsheet once somebody sends you one, from the first look to a summary you can defend.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02The average, as most people write it
- 03A zero is not a one-to-five rating
- 04The percentage with three right answers
- 05Say what you counted