I remember the first time that I was called out for doing this exact thing. Averaging an average in a spreadsheet. Now the crazy thing is that I had been doing it for months and maybe even years in a business intelligence role.
Before you get all judgy this was many, MANY years ago.
Anyhow, it definitely took me by surprise, and I was very embarrassed when I was called out. But as it turns out, the business leader that was using my reports for their presentation was even more embarrassed. So what’s the big deal? Let’s dive into why average of averages is wrong?
An average of an averages is inaccurate because it does not take the units that make up the underlying values into consideration. Each value being averaged is weighted equally even if one value has 2 records and a second value has 1,000 records.
This is an example where a visual walk-through is more helpful, so check out this video for an illustration of how this works.