Excel’s AVERAGEA Function: Evaluate Text Values in Average Calculations

AVERAGEA is a great function to use if you want to evaluate text values as part of an average calculation. The AVERAGE  function ignores text values but AVERAGEA evaluates them as zeros.

The scenario shown in the video below demonstrates why this might be useful: we need to calculate the average sales figure across our workforce.  Where a figure has not been entered we want to give a reason like “annual leave” or “admin day” and evaluate those text values as zero.

AVERAGEA also evaluates TRUE and FALSE values: TRUE as one and FALSE as zero. TRUE and FALSE are given as the result of logical tests, for example the number of sales people that met the monthly sales target, TRUE if they did, FALSE if they didn’t. By finding the average of the ones and zeros, you in effect calculate the percentage of the workforce that met their sales target.

The table below shows the difference between the AVERAGE and AVERAGEA functions.