Syntax. AVERAGEA(value1,value2,...)
Definition. This function calculates the average of the values in an argument list. Not only numbers but also text and logical values (TRUE and FALSE) are included in the calculation.
Arguments
value1 (required) and value2 (optional). At least one and up to 255 arguments (30 in Excel 2003 and earlier versions) for which you want to calculate the average
Tip: Show a zero in cells with zero values
Remember that empty cells are treated differently from cells with the value 0. Empty cells are not counted, but 0 values are. If you activate the zero values option for cells with the value 0, you can see which cells are really empty and which contains the value 0. To do this in Excel 2003, click Tools, select Options, and click the Zero Values check box on the View tab of the Options dialog box. In Excel 2007, click the Office button and select Excel Options (in Excel 2010, click the File tab and then select the Options button). In the Excel Options dialog box, click Advanced. Under “Display options for this worksheet,” select the “Show a zero in cells that have zero value” check box (see Figure 12-3).
Background. You will find more information about the average in the definition of AVERAGE().
The following applies to the AVERAGEA() function:
If arguments containing text are specified as arrays or references, they evaluate as 0.
Arguments that contain TRUE evaluate as 1, and arguments that contain FALSE evaluate as 0.
If you do not want to include text values as part of the calculation, use the AVERAGE() function.
Example. You work in the controlling department of the software company and create an Excel table containing the sales for the past twelve months. Because the list also includes text and logical values, you calculate the average sales with the AVERAGEA() function.
In the first column, you enter the text Closed for February (see Figure 12-4). Because the AVERAGEA() function converts text automatically into the value 0, all 12 values are added, and the sum is divided by 12. The result is $916.67.
The second column contains TRUE instead of Closed. This logical value is evaluated as 1. Therefore, again all 12 values are added, and the sum is divided by 12. The result is $916.75.
If you used the AVERAGE() function, the text values wouldn’t be included; only 11 values would be added, and the sum divided by 11. In this case, the result would be $1,000.00.