AVERAGE function

In this lesson you can learn how to use Average function.

This function calculates the average for the entered numbers or ranges. The advantage of this function is that it can enter addresses of individual cells and also the whole range. Average is also available in the status bar.

Syntax is: =AVERAGE(number1,[number2],...)

Excel functions average function

Take a look at the picture above.

Function AVERAGE doesn't care about empty cells and text in the cell. In the first and third table function AVERAGE there is a different average than in the second table.

It is very easy to make a mistake. Remember about zeros in cells.

 

Example 1 Average Between Two Values

{=AVERAGE(IF((A1:A10>=B1)*(A1:A10<=B2),A1:A10))}

B1 and B2 are the values between which you want to calculate average.

This is an array formula so accept it by CTRL + SHIFT + ENTER.

 

Example 2 To avoid errors

Use this formula when you can haven't got any data in your worksheet.

=IF(ISERROR(AVERAGE(A1:A5)),"No Data",AVERAGE(A1:A5))

If your data in A1:A5 are missing, Excel will show "No data". If it is ok with your data there will be average calculated.

 

Example 3 Average of 10 largest values

{=AVERAGE(LARGE(A1:A50,ROW(1:10)))}

This formula gives you the average of 10 largest values in A1:A50 range as the result.

This is an array formula so accept it by CTRL + SHIFT + ENTER.

 

Example 4 Average with some data missing

How to calculate values average in case of zeros or missing data. Use that formula:

{=AVERAGE(IF($A$1:$A$5<>0,$A$1:$A$5))}

This is an array formula so accept it by CTRL + SHIFT + ENTER.

This formula doesn't count blanks and zeros. In this case average is (2+5+6+3):4. Formula doesn't calculate value in A2 cell. There will be the same when A2 will be blank or text.

Excel Average zeros