What do you mean by “Average”?
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).
=AVERAGE(B2:B8)
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:
=SUM(B2:B8)/7
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).
If you have texts in your range, and you want to count text in your average calculation, use AVERAGEA
=AVERAGEA(B2:B8)
Got the difference? In Excel world, we need to be specific!
I will talk about another common average trap – Average the average.
wmfexcel 