*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…)**

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

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.