INTRODUCTION
AVERAGEIFS FUNCTIONS comes under the STATISTICAL FUNCTION group of Excel.
If we remember the SUMIFS function, AVERAGEIFS function is almost similar to that function.
AVERAGEIFS FUNCTION is different from AVERAGEIF in the number of criterion available. In this function , we can give multiple criterion before the result is given by the function.
e.g.
Suppose we need to find out the average age of the persons present in a party but only those persons whose age is >50.
This can be done directly using this function. Later in this article we’ll see this using an example.
PURPOSE OF AVERAGEIFS FUNCTION IN EXCEL
AVERAGEIFS FUNCTION finds out the average of the terms from the data which satisfies the given conditions [MORE THAN ONE CONDITION ].
PREREQUISITES TO LEARN AVERAGEIFS
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- Basic understanding of rows and columns in Excel.
- Some information about the statistical terms is an advantage for the use of such formulas.
- Of course, Excel software.
Helpful links for the prerequisites mentioned above What Excel does? How to use formula in Excel?
SYNTAX: AVERAGEIFS FUNCTION
The Syntax for the function is
=AVERAGEIFS(AVERAGE RANGE, CRITERIA 1 RANGE , CRITERIA 1,CRITERIA 2 RANGE, CRITERIA 2…)
AVERAGE RANGE The main range on which operation will be done
CRITERIA 1 RANGE The range on which criteria will be applied
CRITERIA 1 Criteria 1
CRITERIA 2 RANGE Second range on which criteria will be applied
CRITERIA 2 Criteria 2…
As many as 127 arguments can be put in this fashion.
EXAMPLE 1: AVERAGEIFS FUNCTION IN EXCEL
DATA SAMPLE
THINK OF A SCENARIO.THERE ARE FIVE PEOPLE WITH PERSON ID 1 2 3 4 AND 5. EACH EARNING BETWEEN $2000 TO $5000.We need to find the average of income of people who earn more than $3000 and age is greater than 14. We are using two criterion here, similarly we can use as many criteria as we want.
This scenario can be solved by manually separating the persons who have income more than $3000 and the persons having age more than 14 and after that calculating average on them or we can try AVERAGEIFS function.
The list is containing five persons 1 2 3 4 5. The income are given in the next column and AGE in the third column.
We have two conditions.
income > 3000
and age >14.
MANUAL CALCULATION:
We can see that
person no. 2 3 and 5 fulfill this condition
now going to the second condition
We see that only 3 and 5 fulfill the condition.
The result comes out to be 9000/2=4500.
USING AVERAGEIFS
We put the formula
=AVERAGEIFS(F27:F31,F27:F31,”>3000″,G27:G31,”>14″)
in H27.
The first argument F27:F31 is the main range on which final operation will be performed.
The next argument is again same F27:F31 as we are putting a check on the income too.
After criteria range is criteria which is “>3000” means income should be greater than 3000.
Next criteria range is G27:G31 which contains the age and after this criteria puts up another
condition as >14.
The result comes out to be 4500 which is as expected
