
PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
CONTENTS
EXCEL FUNCTIONS-AVERAGEIFS
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.
mul
PURPOSE OF AVERAGEIFS FUNCTION IN EXCEL
AVERAGEIF FUNCTION finds out the average of the terms from the data which satisfies the given 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.
SYNTAX: AVERAGEIFS FUNCTION
The Syntax for the function is
=AVERAGEIFS(AVERAGE RANGE, CRITERIA 1 RANGE , CRITERIA 1,CRITERIA 2 RANGE, CRITERIA 2…)
EXAMPLE 1:AVERAGEIFS FUNCTION IN EXCEL
DATA SAMPLE
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 adn 5 fulfil 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.
OTHER WAYS TO REACH THIS ARTICLE
WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL
YOU MAY LIKE
- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.