spot_img

AverageIf

 

The AVERAGEIF function in Excel calculates the average of cells that meet one criteria. AVERAGEIFS calculates the average of cells that meet multiple criteria.

1. For example, the AVERAGEIF function below (two arguments) calculates the average of all values in the range A1:A7 that are greater than 0.

Calculate Average If Greater Than 0

2. The AVERAGEIF function below (three arguments, last argument is the range to average) calculates the average of all values in the range B1:B7 if the corresponding cells in the range A1:A7 contain exactly Apple.

AverageIf with Text Criteria

3. The AVERAGEIF function below calculates the average of all values in the range B1:B7 if the corresponding cells in the range A1:A7 do not contain exactly Banana.

Calculate Average If Not Equal To

4. The AVERAGEIF function below calculates the average of all values in the range B1:B7 if the corresponding cells in the range A1:A7 contain a series of zero or more characters + berry. An asterisk (*) matches a series of zero or more characters.

AverageIf Function with Asterisk

5. The AVERAGEIF function below calculates the average of all values in the range B1:B7 if the corresponding cells in the range A1:A7 contain exactly 4 characters. A question mark (?) matches exactly one character.

AverageIf Function with Question Marks

The AVERAGEIFS function (with the letter S at the end) in Excel calculates the average of cells that meet multiple criteria.

6. The AVERAGEIFS function below calculates the average of all values in the range A1:A7 that are greater than or equal to 500 and less than or equal to 1000.

Calculate Average If Between Two Numbers

Note: first argument is the range to average, followed by two or more range/criteria pairs.

7. The AVERAGEIFS function below calculates the average of all values in the range C1:C7 if the corresponding cells in the range A1:A7 contain exactly Apple and the corresponding cells in the range B1:B7 contain exactly Red.

AverageIfs with Text Criteria

Note: again, the first argument is the range to average, followed by two or more range/criteria pairs.

spot_img
Previous articleWhat-If Analysis in Excel
Next articleCreate a Calendar in Excel