How to Use Different Average Functions in Excel

In using Excel functions, you will certainly need to use the different average functions. In this article, we will explore the usages of all the average functions.

There are 4 variants of average functions: AVERAGE, AVERAGEA, AVERAGEIF and AVERAGEIFS. In the following part, we will talk about them in details.

1.    AVERAGE

AVERAGE function is used to calculate the average of a range. This function can also calculate the average of the arguments in formulas. Now we will use the example below to show the usage. In this image, you need to calculate the average of the sales volume.

1. Click a blank cell in the worksheet. Here we click the cell B14.
2. Now input the formula into the cell:

=AVERAGE(B2:B13)

The range in the formula contains the sales volume of the product “DataNumen Excel Repair”.

3. And then press the button “Enter” to move the cursor to other cell. And immediately you will get the result in the cell.

When it comes with multiple arguments in the formula, this function will calculate the average of all the arguments. Here it doesn’t mean that it will first calculate the average of every argument and then calculate the average of those average numbers. You can see the example below to see how it works.

1. Still we click the cell B14 in the range.
2. Now input this formula into the cell:

=AVERAGE(B2:B13,1)

3. And then you will get this result in the cell after you press the button “Enter”.

The result of the two arguments is “284.57”. It will not use the calculation formula “(308.2+1)/2” to calculate the average of those average numbers of arguments. Therefore, the next time if you use many arguments in this function, you need to be more careful about this key point.

2.    AVERAGEA

AVERAGEA can calculate the average of a range that contains logical values or text representations of numbers. And this is the difference between AVERAGEA and AVERAGE function. Here we will use a simple example to illustrate the usage. We will use the same worksheet that contains the sales volume.

In this image, there is a logical value “FALSE” in the cell B13. And we need to calculate the average of the range B2:B13.

1. Click a blank cell in the worksheet.
2. And then input the formula into the cell:

=AVERAGEA(B2:B13)

3. Next press the button “Enter” on the keyboard. Therefore, you will also get the result in the cell.

The formula in cell B14 is AVERAGE function. Thus, the results of the two functions are different. Because here the AVERAGEA will treat the “False” as 0, while the AVERAGE will not include the value in cell B13. Thus, when you need to calculate the average of a range with logical values or text representations of numbers, you need to use the AVERAGEA instead of AVERAGE function.

3.    AVERAGEIF

If you need to calculate the average of a range with certain criterion, you can use the AVERAGEIF function. Still we will use the worksheet that contains the sales volume. Suppose here we calculate the average of values and those values should be larger than 300.

1. Click a blank cell in the worksheet.
2. And then input this formula into the cell:

=AVERAGEIF(B2:B13,”>300″)

In this formula, the range and the average range is the same.

3. And then press the button “Enter” on the keyboard. Now you can check the result in the cell.

Besides, you can also use wildcard characters in the formula.

1. Click a blank cell in the worksheet.
2. Next input this formula into the cell.

=AVERAGEIF(A2:A13,”=J*”,B2:B13)

You want to calculate the average of months with the beginning “J”, thus you will use the character “*”. And the average range is B2:B13. Thus, you need to input it into the cell.

3. And then press the button “Enter” to move the cursor to other cell. Now you will also see the result in the worksheet.

4.    AVERAGEIFS

In AVERAGEIF function, you can only use single criterion. Sometimes you need to use multiple criteria to calculate the average of a range. Hence, you can use the AVERAGEIFS function. Now we will use this example to discuss about the usage.

1. Click a blank cell in the worksheet.
2. Next input this formula into the cell:

=AVERAGEIFS(D2:D13,B2:B13,”>300″,C2:C13,”>400″)

In this formula, there are two criteria. And the average column is the first argument in this formula. In your actual formula, each criteria range should have the same numbers of rows and columns. When all of the criteria are met, the cell will be calculated.

3. After that, press the button “Enter” again. Thus, you will get the result in the target cell.

In addition, you can also use wildcard characters in this function.

And for more practical application of the AVERAGEIF and AVERAGEIFS functions, you may also refer to our previous article How to Average Values Satisfying Specified Conditions with AVERAGEIF and AVERAGEIFS Functions.

The above are the details of all the average functions in Excel. When you need to calculate average, you can choose one of those functions according to your need.

A Powerful Repair Tool can Retrieve your Data after a Virus Attack

Virus has now become more severe to us. And you may have already heard of virus attacks and the serious results. Thus, it is necessary for you to prepare in advance. And among all the methods, you can use a third party tool. Once you meet with a virus attack, you can use this tool to repair xlsx corruption and other errors in Excel. With this tool at hand, you will never need to worry about your data and information.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair docx corruption and outlook repair software products. For more information visit www.datanumen.com