AVERAGEIF function of excel is a kind of conditional AVERAGE formula of excel. The AVERAGEIF function is a very useful function when we want to find the average but not for the entire array of numbers. It is useful when you want to find the average of numbers in excel based on some criteria.
Let us learn how to use the excel AVERAGEIF formula with its, syntax, argument, and examples.
- When To Use AVERAGEIF Function In Excel
- Syntax and Arguments
- Examples To Learn Excel AVERAGEIF Formula
- Points To Remember About Excel AVERAGEIF Function
Here we go 😎
When To Use AVERAGEIF Function In Excel
Many a time, while finding the average of values in excel, we might not be willing to get the average of the entire array of elements. Instead, we may wish to specify some condition before finding the average. This is when the AVERAGEIF function of Excel comes into its use.
AVERAGEIF function is one of the inbuilt statistical functions of excel.
We can use the AVERAGEIF formula to find the average sales only for premium packages of a company. It is used to find the average scores of students having marks more than 60.
Syntax and Arguments
This section of the blog explains the function arguments of the AVERAGEIF function.
- range – In this argument, specify the range on which to apply the condition on. If the argument average_range is not specified, then this range will be used as average range.
- criteria – In this argument, specify the condition to check for. It may have logical operator, wild card characters, cell refrence, numerical value or text string.
- [average_range] – This is an optional argument. We use this argument when criteria range (i.e. the first argument) is not the range having the numbers to find their average.
Examples To Learn Excel AVERAGEIF Formula
Let us now take some examples to understand the AVERAGEIF formula in excel.
Ex. 1 – Simplest Example for AVERAGEIF Function in Excel
The below image shows the annual marks of students out of 500.
Let us find the average of all those marks which are greater than 166. Use the following AVERAGEIF formula, as below:
As a result, the function has returned 307.6
Explanation – In this example, the criteria range (on which we apply criteria) and average range (where we have values to find average) are the same i.e A2:A11. The highlighted cells are the records that meet the criteria “>166”. After that, the function calculated the result as the mean of marks greater than 166.
Ex. 2 – Passing Optional Argument to Excel AVERAGEIF Function
When we want to apply our criteria on the range which is different from the range containing the actual numerical value, we shall specify the criteria ranges and average ranges separately.
The below list contains the sales quantity for different of car spare parts.
Use the following AVERAGEIF formula to find the average sales of only the ‘Wiper’.
As a result, the function has returned 316.
Explanation – The first argument A2:A8 is the criteria range where we applied the criteria “Wiper” (function is not case sensitive). Highlights are the records corresponding to “Wiper”. The third argument is the average range B2:B8. The function has calculated the mean of corresponding highlighted values (Qty) that meet the criteria.
Ex. 3 – Using Logical Operators in AVERAGEIF Function Of Excel
Let us suppose there is a bakery that sells pastries, cakes, cupcakes, and toffees, and below are their financials.
The owner of the bakery wants to know the average sales of pastries, cakes, and cupcakes only.
Therefore, it is clear that we need to exclude the sales of toffees from averaging.
To achieve this, use the not equal logical operator in the following AVERAGEIF formula to know the average sales of only cakes, cupcakes, and pastries.
As a result, the function has returned 40399.
Explanation – The criteria “<>Toffees” is applied on the range A2:A5. The not equal logical operator (<>) will make the function do an average of records that do not correspond to “Toffees” ( see the highlighted records). The Average Range is B2:B5. The function has calculated the average result of cells B2, B3, B5 as they meet the criteria.
Other logical operators are:
Ex. 4 – Finding Average Value Corresponding to Blank Cells
While doing data analytics, many a time we need to calculate the averages of the cells next to blank cells.
Let’s suppose in a marriage bureau, the matches are assigned to an employee.
The matches that are not assigned to any employee are left as blank cells.
Enter the following AVERAGEIF formula to find the average number of matches that are not assigned to any employee.
The result of the above formula is 54.
Explanation – The Criteria range is B2:B10 where we apply the criteria “=”. The criteria “=” is used for the blank cells. The highlighted are the records that correspond to blank cells. The corresponding values in the red rectangles are the cells of average range C2:C10 that meet this condition. As a result, the function has calculated their average ( matches not assigned to any employee) to be 54.
Points To Remember About Excel AVERAGEIF Function
These points are very important to keep in mind while using the AVERAGEIF function of Excel.
- The function is not case sensitive.
- We can change the number of decimal places of the AVERAIF formula result from thr fomat control dialog box.
- The AVERAGEIF function ignores the text string and logical values (TRUE and FALSE) in the average range or the range (when average range is not specified).
- The function returns a #DIV/0! excel error code when
- range and average range are not of same size.
- no cell matches the condition.
Thank you for reading. 🙂
- AVERAGE Function in Excel – Finding Mean or Average Value in Excel
- RANK.AVG Function in Excel – Usage, Error Handeling
- TRIMMEAN Function in Excel – Truncated Mean
- MEDIAN Function in Excel – A Statistical Parameter
- HARMEAN Function in Excel – Get Harmonic Mean
- AVEDEV Function in Excel – Definition and Examples