Average Trap

What do you mean by “Average”?

Excel Tips - Average Trap 1

When the question “What is the average of…” is asked, the top of mind function could possibly be AVERAGE.  But did you know that AVERAGE function ignores text (including number stored as text), logical values and empty cells??… OMG no? Then you have probably made a lot of mistakes in calculating average in your Excel spreadsheet.

Actually it depends on the question asked.  If we are talking about the average of available numbers, AVERAGE does the job nicely as we don’t have to worry about the blanks or texts in then range(s).


However if we are talking about daily average in a week as demonstrated in our example, the denominator should be 7 instead of 5.  (We want to understand the daily average of a week, not just the days with sales).  Then the formula should be:

Tips: When working with large (or flexible) range, we may replace 7 by ROWS(B2:B8)

If you insist to use AVERAGE, fill all blank cells with zero first.  AVERAGE takes zero (as 0 is a number; and to report no sales it is better to input 0 rather than leave it blank).

Excel Tips - Average Trap 2

If you have texts in your range, and you want to count text in your average calculation, use AVERAGEA


Excel Tips - Average Trap 3

Got the difference? In Excel world, we need to be specific!

I will talk about another common average trap – Average the average.


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.

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 )

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.