An alternative solution to the AverageIf function and the lower version in Excel

This paper introduces the usages and examples of the AverageIf function in Excel, and gives the AverageIf function substitution solution in the 2003 low version.

The AverageIf function in Excel is a new addition to the 2007 version of the function that averages the condition.

According to official help, the AverageIf function returns the average of all cells within a range that meet a given condition.

The syntax of the AverageIf function is: averageif (condition range, condition, actual cell that calculates the average)

Let's take a practical example to learn how to use AverageIf functions in Excel.

Above, calculate "1" average sales, D3 cell input formula: =averageif (A2:A10,A2,B2:B10), enter OK.

In the formula, the A2:A10 condition area, that is, all department cases in the Department column. The condition refers to "1 part", namely satisfies the Department "1 Department" the department, B2:B10 is corresponds to the actual computation average value area.

Using the excel2003 version of the friend, there is no averageif function, and how to use the rest of the formula to replace the AverageIf function?

The following two solutions are available:

First, enter the formula: =average (IF (A2:A10=A2,B2:B10)), press the Ctrl+shfit+enter key combination to end, that is, the array formula.

Explanation: First use if to judge, if satisfies the condition to return the corresponding value, does not satisfy returns false, then uses the average function to calculate these values to average.

Second, the formula is: =SUMIF (A2:A10,A2,B2:B10)/countif (A2:A10,A2)

Explanation: The sum of the areas that satisfies the condition is divided by the number of conditions.

In addition to the average function, the high version also adds a averageifs function that returns the average of all cells that meet multiple criteria.