Excel calculated that 0.33 of a child had visited a health clinic — and oddly enough, it wasn’t wrong…
You’re probably thinking ‘don’t be stupid Fiona, you can’t have a third of a child’. And that is exactly what I thought too.
I was analysing 65,000 rows of anonymised patient data from health clinics run by the Baphalali Eswatini Red Cross in Southern Africa. This was to support healthcare staff with assessing overall community health levels in 2020/21, for both reporting and future planning.
One part of this was working out how many patients had visited the clinic once, twice, three times, and so on. And also, how many of these visits were made by young children aged 5 and under.
Through analysing this data in an Excel spreadsheet with a series of “COUNTIFS” formulas and pivot tables, I calculated the total number of visits which were single or repeat visits. So far, so good.
I then divided the total number of repeat visits by the visit frequency (once, twice, etc), to work out the number of individual patients who had visited the clinics x number of times.
However, this yielded some strange results for the young children. For example, 614.33 children had visited the clinics 3 times, and 193.83 children had visited 6 times?
I thought that something must be wrong. After questioning whether I had mistakenly used the wrong formulas or calculations for the question I was trying to answer, and having successfully tied my brain in knots, I called in reinforcements in the shape of Paul Knight.
We double checked that all the formulas I had used were the right ones for the job, and for any typos. And then that they were correctly applied to all 65,000 rows of data. And everything was at it should be. Hmmm.
After a lunch break to rest our retinas, we pulled in a third pair of eyes for a fresh look, in the form of David Callaghan.
Having triple checked everything and trying different formulas (“IF”, combined with “AND”) which yielded the same decimal children; then scratching our heads for a good half hour, we were all completely and utterly stumped.
Until suddenly, Paul Knight had a eureka moment. We were so deep in the maths, that we had overlooked the human realities of the data — the fact that we all get older.
614.33 children aged 5 and under visited the clinic three times, because that final child was 5 years old for their first visit, but had passed their 6th birthday for the second and third visit.
And just like that, we all felt a bit stupid, and couldn’t believe we hadn’t realised that something so obvious was the source of the error. But actually, it wasn’t an error. Ultimately, Excel is a calculator — it cannot be wrong. Although it wasn’t exactly right either.
As with any tool, it is important to keep in mind the human context to the data being analysed.
When we try to solve human problems with technology, we often simplify human life into snapshots that don’t fully reflect reality. And that is when the tool has reached its limit. Remember, context is everything!
