The AVERAGEA function is to calculate the average of a group of numbers when you want to include those cells with text.
= AVERAGEA (Number1, Number2, …)
The Number1 is required, which is the first number in the group you want to average;
The Number2 is required, which the second number in the group you want to average.
… (Other Numbers) is optional, any other numbers in the group you want to average.
The AVERAGEA function will count the cells that contain text data, but blank cells will be ignored in the calculation. To count cells with only numbers, please refer to the AVERAGE function.
Example 1: What is the average of Cell A2:A5?
= AVERAGEA (A2:A5) The average of Cells A2:A5 is 5.5.
Example 2: What is the average of cell A4:D4?
In this example, cell C4 is blank and is ignored when calculating the average using the AVERAGEA function.
Sum (A4 to D4): 6+3+3 = 12
Count (A4 to D4): 3
Average: 12 ÷ 3 = 4
= AVERAGEA (A4:D4) The average of cells A4:D4 is 4.
Example 3: What is the average of cell C2:C5?
In this example, we can see that C5 contains text and it is counted in the average calculation using the AVERAGEA function
Sum (C2 to C5): 6+5 = 11
Count (C2 to C5): 3, C5 with text will also be counted, but C4 empty is not counted
Average: 11 ÷ 3 = 3.6667
= AVERAGEA (C2:C5) The average of Cells C2:C5 is 3.6667.
Quick Note: AVERAGE and AVERAGEA function
– Both functions will not count blank cells.
– AVERAGE function only counts cells with numbers and cells with text or blank will be ignored; while the cells with text will be counted with AVERAGEA function.