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