Excel AVERAGE (formula)
For statistics, the AVERAGE function in Excel is irreplaceable. It combines not only the addition of all selected values, but also the counting of the cells to divide the values by them. This alone shows that the AVERAGE function can be used in Excel to combine several steps that would otherwise have had to be performed manually. However, it is often criticized with average value statistics that outliers distort the result strongly in one direction. In this case, it is possible to exclude these values using a logic function based on the average value function. Thus, more accurate results can be measured that concern the large quantity.
Examples of use of the AVERAGE formula in Excel:
|Average of single values:||=AVERAGE(3,7,93,125,202,…)|
|Average of several cells:||=AVERAGE(A3,B8,C2,Z49,…)|
|Average of one row:||=AVERAGE(6:6)|
|Average of a column:||=AVERAGE(Y:Y)|
|Average value of a range:||=AVERAGE(A4:U24)|
Excel average value without 0 / average value without outliers:
Note that the average function includes outliers as well as entries with 0. However, this can falsify your result very much, if 0 stands for a missing record, for example. In such cases, you must remove entries with 0 (or your outliers) from the equation.
For this purpose, the function AVERAGEIF is used, since a condition can be attached to it, such as only using values that are greater than 0. We have written a separate post about the AVERAGEIFfunction if you want to know exactly how it works and its syntax.
|Number can be an absolute value, a (relative or absolute) cell reference, or a cell range (see examples above)|
|Other values. A total of up to 255 arguments can be processed in an AVERAGE function|
Regardless of whether AVERAGE or AVERAGEVALUE – these functions are indispensable for statistics and focus analyses in Microsoft Excel. For a more detailed explanation, it may also be worth taking a look at our explanatory video, in which we explain the exact procedure using examples and the viewer can thus often understand the connections even better. In general, we think that the average value function is one of the basic formulas that every user of Microsoft Excel should know.
Links with * are affiliate links. By purchasing via this link we receive a part of the purchase price as commission without any additional costs for you. This helps us to expand the offers available here for you! Thank you!