AVERAGEIF

Hi Friends,

In my last article, I discussed about AVERAGE function in Microsoft Excel, a very basic statistical function (arithmetic mean).

But what if, if I need the average of some given numbers if some condition is met. Let me explain it with an example. 

Suppose I need the average of all the Sales and Target values of South zone from the data provided where North, East West and South zone’s figures are plotted one after another in consecutive rows (as given in the below picture).

Here I have two options, one, using IF function nested with Average or I need some special Function which will allow me to average out those numbers where one given condition met.Today, in this article, I will discuss about the built-in function in Excel which will provide me the average value of some number if certain condition is met. The function is AVERAGEIF.

Let us discuss about it today…

Description

The Microsoft Excel AVERAGEIF function returns the average (arithmetic mean) of all numbers in a range of cells, based on a given criteria.

The AVERAGEIF function is a built-in function in Excel that is categorized as a Statistical Function. The AVERAGEIF function can be entered as part of a formula in a cell of a worksheet.

Later, I will show you how to use this function nested with other functions.

Syntax

The syntax for the AVERAGEIF function in Microsoft Excel is very similar to SUMIF. If you want to know how to use SUMIF, please click in this link.

=AVERAGEIF(range, criteria, [average_range] )

Parameters or Arguments

range

The range of cells that you want to apply the criteria against.

criteria

The criteria used to determine which cells to average.

Note : If the Criteria is in text or if you are using any operator please use Double Quotes.

average_range

Optional. It is the cells to average. If average_range is omitted, it uses range as the value for this parameter.

Returns

The AVERAGEIF function returns a numeric value.

Example 1 :

How to use AVERAGEIF with TEXT/STRING data type ?

In the below image, I want to get the Average of the Sales & Target value of South Zone only. The formula for this is

=AVERAGEIF(B2:C15,"South",C2:C15)            For Target portion

=AVERAGEIF(B2:C15,"South",D2:D15)            For Sales portion

Please note I mentioned South within double quotes.

How to use AVERAGEIF with TEXT or STRING as Criteria

Example 2 :

How to use AVERAGEIF with OPERATOR like =, <, > etc. ?

In the same example, let suppose I need the AVERAGE of sales value where TARGET value is Greater than 150.

Note : Here the rage I provided to get the average is D2:E15, because I want my AVERAGE function to search for 150 in the Target column which is Column D and the average will come from the next column E which is the Sales column.

The below image will make it very clear.

How to use AVERAGEIF with OPERATORS as Criteria

Hope this article is enough for you to understand the AVERAGEIF function. If any question arises, feel free to comment in the comment box below.

Thanks for reading…