How to Use the Average Function in Excel
In this lesson, you can learn how to use the Average function. The average function in Excel is a feature that would use the arguments to return with average of those arguments. However, it would only use the cells that are containing numbers to calculate the average.
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.
What is the AVERAGE function in Excel?
AVERAGE stands for “Average”. It is a function that takes a range of numbers as an argument and returns the average value. The average is calculated by adding all the numbers in the range and then dividing by the number of numbers.
How does the AVERAGE function work?
The AVERAGE function works by first adding all the numbers in the range. Then, it divides the sum by the number of numbers.
Average function syntax
Parameters (Syntax) The syntax of average has one obligatory argument, while others are optional.
The syntax looks like this: =AVERAGE(number1; [number2];…).
Number1: This is a required argument, and could be cell reference, a range that you would like to have the average of.
Number2: This is optional. It could also contain reference, range, and these ones reach maximum of 255.
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.
Examples of Average function
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.
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.
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.
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.
Tips for using the AVERAGE function in Excel
The AVERAGE function is a non-volatile function, which means that it does not recalculate its value whenever the workbook is recalculated. This can be useful if you want to keep the average of the numbers up to date, but it can also be a problem if you are using the AVERAGE function in a formula that should not be recalculated every time the workbook is opened.
The AVERAGE function can be used to calculate the average of any range of numbers, regardless of their size or shape.
The AVERAGE function can be used in conjunction with other functions to perform more complex calculations. For example, you could use the AVERAGE function to calculate the standard deviation of a set of data.
Leave a Reply