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.

Editor's Picks