How To Use Excel AVERAGEIF Function –A Quick Guide

An average is the most used mathematical function in our daily life.

It can be during the score calculation or financial maths, we end up using the average most of the times.

Excel AVERAGEIF function can come handy during the calculations where you need to apply some filtration before finding the average.

Let’s see how to use it in detail…

1. What is the AVERAGEIF Function?
2. What is the Syntax?
3. How to use it with an example?

1. What is the AVERAGEIF Function?

An AVERAGEIF function can be used to find the average of values supplied as arguments to it based on some criteria.


2. What is the Syntax?

=AVERAGEIF(range, criteria, [average_range])

  • range  = (Required) One or more cells to average, including numbers, arrays, or references that consists of numbers.
  • criteria = (Required) The criteria based on which the average has to happen.
  • [average_range] = (Optional) The range on which the actual average has to happen. If ignored, the average will happen on ‘range’.

Note :

  • Cells in the range that contain TRUE or FALSE are ignored.
  • Any empty cells supplied as [average_range] will be ignored.
  • If the value supplied to the range is a blank or text value, AVERAGEIF returns the #DIV0! error value.
  • If a cell containing empty value is supplied as criteria, AVERAGEIF treats it as a 0 value
  • If no values in the range match the criteria, the result will be #DIV0! error.
  • Wildcard characters such as question mark (?) and asterisk (*)also can be used in criteria.

3. How to use it with an example?

Assuming we have the data of a few students along with their location and scores and are required to find the average of their scores.

Let’s see how to do it!!!

Excel AverageIf function data

1. Average of  numbers based on criteria.
2. Average of  numbers based on criteria applied to another range.
3. Average of  numbers based on criteria(Wildcard) applied to another range.

1. Average of numbers based on criteria

Excel AverageIf function 3

**Explained**

=AVERAGEIF(C3:C7,">7")
  • C3:C7 = range of cells containing numbers for which the average has to be calculated.
  • “>7” = Condition based on which the average has to happen.

 


2. Average of numbers based on criteria applied to another range.

Excel AverageIf function 1.PNG

**Explained**

=AVERAGEIF(B3:B7,"US",C3:C7)
  • B3:B7 = range of cells containing data on which the criteria has to be applied.
  • “US” = Condition based on which the average has to happen.
  • C3:C7 = range of cells containing numbers for which the average has to be calculated.

 


3. Average of numbers based on criteria(Wildcard) applied to another range

Excel AverageIf function 2.PNG

**Explained**

=AVERAGEIF(A3:A7,"*S*",C3:C7)
  • A3:A7 = range of cells containing data on which the criteria has to be applied.
  • “*S*” = Condition based on which the average has to happen(WILDCARD).
  • C3:C7 = range of cells containing numbers for which the average has to be calculated.

 

Hope it has helped you… 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: