There are more than a dozen methods to calculate the average value in excel. What you can't calculate may be the wrong selection of source data, wrong format (there are spaces or the source data you want to calculate is not in digital format, so you can't get the desired result), and the cells are hidden.

The following are some common methods, depending on the needs.

Take A 1: A5 as an example:

1.=AVERAGE(a 1:a5), and all cells containing numbers in a 1:a5 are counted. If a 1= "Text ",it is not calculated.

2.=AVERAGEA(a 1:a5), calculate the average of all non-empty cells in a 1:a5. If a 1= "Text ",the denominator will also be calculated.

3.=AVERAGEIF(A 1:A5, ">0", A 1:A5), calculate all values greater than 0 in a 1: a5.

4.= subtotal (1, A 1: A5), the same as 1.

5.= subtotal (10 1, A 1: A5), calculate the average value of all cells in A 1: A5.

6. = sum (a 1: a6)/count (a1:a5), exactly the same as1.

7. = sum (a1:a6)/counta (a1:a5), which is exactly the same as 2.

In addition, it can also be used in combination with functions such as sumif, sumifs, sumproduct and averageifs to get results. Of course, the first two are the most commonly used.