Average Trap 2 – Average the average

Why we cannot take the average of averages?

Excel Tips - Average the average 1

Once upon a time, I was questioned with the above.  First thing on my mind: “Is it a test?”… So I answered the question carefully, as shown in the screen shot below: Excel Tips - Average the average 2

I think this is quite basic and straight forward.  Isn’t it? To my surprise, it is not.  Many people would take simple average for averages or rates.  Here’s another example: Excel Tips - Average the average 3 To get the correct answer, we have to find out the total number of  Transactions, then divided it by total number of Traffic.  Simple average would NOT give you the grand average for this kind of data.  If the components (Traffic or Transaction) are missing, there is no way we can get the correct conversion rate for all shops.

Tips: If you are using Pivot Table to summarize the above information, DO NOT use the field “Conversion Rate” directly.  You have to do it by Calculated Field.

Mind the average trap for various “Rates”.  Did you experience or witness this kind of trap in your workplace? Feel free to share with us in the comment.

Different people have different definitions of “Basic”.  Don’t take “Basic” for granted. 🙂


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Formula and tagged . Bookmark the permalink.

1 Response to Average Trap 2 – Average the average

  1. Pingback: Average Trap | wmfexcel

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.