INTRODUCTION
AVERAGEIF FUNCTIONS comes under the STATISTICAL FUNCTION group of Excel.
If we remember the SUMIF function, AVERAGEIF function is almost similar to that function.
AVERAGEIF FUNCTION gives an option to find out the option of the data if a particular given criteria or condition is fulfilled. It gives us the control over filtering the data in the function itself and save our time.
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 AVERAGEIF FUNCTION IN EXCEL
AVERAGEIF FUNCTION finds out the average of the terms from the data which satisfies the given condition.
PREREQUISITES TO LEARN AVERAGEIF
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: AVERAGEIF FUNCTION
The Syntax for the function is
=AVERAGEIF(RANGE , CRITERIA , AVERAGE RANGE)
RANGE RANGE IS THE FULL DATA SET TO BE ANALYZED
CRITERIA CONDITION ON WHICH THE DATA WILL BE SELECTED FOR THE OPERATION
AVERAGE_RANGE THE FINAL SET OF DATA ON WHICH OPERATION WILL BE DONE
SYNTAX 2
=AVERAGEIF(RANGE , CRITERIA )
RANGE RANGE IS THE FULL DATA SET TO BE ANALYZED
CRITERIA CONDITION ON WHICH THE DATA WILL BE SELECTED FOR THE OPERATION
*IN SYNTAX 2, THE RANGE WILL ACT IN THE PLACE OF AVERAGE RANGE TOO. I.E. THE CHECK WILL BE PERFORMED ON THE RANGE AND OUTPUT WILL BE CALCULATED ON THE RANGE ITSELF ONLY.
EXAMPLE 1:AVERAGEIF FUNCTION IN EXCEL
DATA SAMPLE
THINK OF A SCENARIO.THERE ARE FIVE PEOPLE. EACH EARNING BETWEEN $1000 TO $6000.We need to find the average of income of people who earn more than $3000.This scenario can be solved by manually separating the persons who have income more than $3000 and after that calculating average on them or we can try AVERAGEIF function.
STEPS TO USE AVERAGEIF FUNCTION (TWO ARGUMENTS)
The data is organized as a table as shown in the previous picture.
The person number , income in dollars and ages are put in the table.
CASE 1:As discussed, we will find out the average of the income, for the persons who earn more than $3000.
The function used is written in H7
=AVERAGEIF(F7:F11,”>3000″)
This will search the range F7 TO F11 and average out the values which are greater than 3000.The answer comes out to be 4333.33. The manual calculation is also shown for the same.
EXAMPLE 2:AVERAGEIF FUNCTION IN EXCEL
STEPS TO USE AVERAGEIF FUNCTION (THREE ARGUMENTS)
CASE 2: AVERAGEIF also gives us an option to put a check on another range and operate on the different range.In the same case, We’ll find out the average age of the people whose income is more than 3000 dollars.
It means, the check will be on another range and operation will be on a separate range.
The formula use is=AVERAGEIF(F27:F31,”>3000″,G27:G31)
In this case, the criteria will be checked for F27 TO F31and the result will be returned by the criteria check on range F27 TO F31 and result will be calculated for the values inG27 TO 31.The result is 36.The manual calculation is also shown in the animated picture.
