PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

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.

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.

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 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.

CONTENTS