Software

When Excel's AVERAGE function doesn't give true results, try AVERAGEA

If your data includes text values, Excel's AVERAGE function won't give you an accurate picture. See how AVERAGEA can solve this problem.

Sometimes, the AVERAGE function does not give a true picture of your data. AVERAGE can only find the average of a range of numerical values; it skips any text values. For example, the results of a monthly mailer for donations to a nonprofit organization are listed below. If you want to know the amount of the average donation that came in, you would use the AVERAGE function, as shown in B20. The results would not include those who received the mailing but did not send a donation.

If you want a better picture of how your donors responded to the mailing, you would use the AVERAGEA function, as shown in B21. AVERAGEA evaluates the text value None as 0 and includes that value in its calculation.

Figure A

Thus, of those who responded to the mailing, the average donation was $145.45. However, if we are to count those who gave $0, the overall average response to the mailing was $106.67.

Figure B


Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

9 comments
mivanos19
mivanos19

You should never have strings or any tex on the calculation area. But if you do not have a different way to structure the spread sheet, try using AVERAGEA function..

Marshwiggle
Marshwiggle

I think some people are missing the point here, which is that Excel is providing two averaging functions, each of which will give a "true" answer, depending upon the question you are asking. If you want to know the average amount given by those who gave anything, use AVERAGE, and it will skip the non-responders for you. If, on the other hand, you have calculated the average cost of each piece of mail, and now want to know whether it was worth it, by comparing the average return per piece, AVERAGEA will provide that answer.

lodestone
lodestone

. . .isn't split finely enough. What this comes down to is whether 'none' means zero or null. Either way, all the conditioner in the world won't fix the split ends in this thread. --Allen

markb582
markb582

No, entering zero is incorrect. I will bring it down even more, it is best to leave the space blank and have the Average, only average those who gave.

C'Town LarryMac
C'Town LarryMac

Of course, this is just as easily done by setting all of those 'NONE's to 0 (zero)

lfschauer
lfschauer

it depends on what the CEO or CFO of the organization want, or what spin the creator of the spreadsheet wants to portray

demhart
demhart

If changing the =AVERAGE(Responses) call to =SUM(Responses)/COUNTIF(Responses,">0") counts as easy in your book...

jacinorwood
jacinorwood

Of course, but when you have non-Tech people wanting spreadsheets a certain way, this function is the answer.

slurpee
slurpee

well, you can get the same value as AVERAGEA using =SUM(A1:A15)/COUNTIF(A1:A15,""), but AVERAGEA is easier....

Editor's Picks