The AVERAGEIF Function in excel is used to find the average of a range of cells that meet a specific condition or criteria. The AVERAGEIF Function is in the family of the various conditional (IF) functions, it was introduced in Microsoft Excel 2007 and although it has a superior successor in the AVERAGEIFS function, it still serves as a very useful and powerful function for data analysis in excel.
The AVERAGEIF Function supports logical operators such as (>, <, =, <=, >=, <>) , and it can return average values for wildcard (?*) conditions for partial matching. Getting confused? don't worry, we would touch on all these in the subsequent sections with detailed examples. let's take a look at the syntax and argument of the AVERAGEIF Function in our next section.
Syntax & Argument
The syntax of the AVERAGEIF Function is as follows:
=AVERAGEIF(range, criteria, [average_range])
There are 3 arguments for the AVERAGEIF Function and 2 are required whilst one may be optional. Let's take a quick look at what they represent:
- range: This represents the range of cells to be evaluated by the criteria.
- criteria: This is the criteria or condition that determines which cells to be included when calculating the average. This condition could be a number, text, date or logical expression selected from a cell reference or hardcoded within the function.
- average_range: This is the range of cells to be averaged and if omitted, the "range" argument above would serve as the range of cells to be averaged. So yes, it could be an optional argument, but please note that your range argument must be made up of at least one numerical value for the average_range argument to be omitted and for the function to still work correctly.
Usage Notes
The AVERAGEIF Function calculates the average of cells in a range that meet a specified condition or criteria, and this criteria could include logical operators such as " >, <, =, <=, >=, <> " and wildcards (?*) for partial matching. Let's see some other facts to note when using this function:
- The average_range argument (or range argument if the former is omitted) must contain at least one numerical value to be averaged.
- The AVERAGEIF Function can handle only single conditions. If you need to check 2 or more ranges for conditions, you need to use the AVERAGEIFS Function.
- The AVERAGEIF Function can lookup more than one condition on a single range but only if the conditions are hardcoded and the formula is outputted as an array formula by pressing CTRL + SHIFT + ENTER rather than just the conventional ENTER.
- If no cell meets the criteria, the AVERAGEIF Function would return a #DIV/0! error.
In the next section, we would be going over various formats for our criteria.
Defining the Criteria
As stated in the previous section, the criteria could be defined with logical operators (>, <, =, <=, >=, <>) and wildcards (*?) for partial matching. It can however be a little tricky to define if you aren't familiar with the syntax when using these logical operators and this is the reason why we are creating this section to take you through the different ways to define your criteria.
Objective | Criteria |
---|---|
Cells greater than 50 | ">50" |
Cells less than 100 | "<100" |
Cells equal to 75 | 75 or "75" |
Cells greater than or equal to 200 | ">=200" |
Cells less than or equal to 150 | "<=150" |
Cells equal to Blue | "Blue" |
Cells not equal to Green | "<>"red |
Cells that are blank | "" |
Cells that are not blank | "<>" |
Cells that begin with T | "T*" |
Cells greater than B5 | ">"&B5 |
Cells Less than Now | "<"&NOW() |
Notice that the last 2 criteria in the table above deploy concatenation with the use of an ampersand. This is because they refer to another cell. The ampersand instructs excel to evaluate whatever formula or value to the right of it and then join with the logical operator for a final evaluation. So in essence, when a criteria includes a value from another cell, logical operators such as ">" or a formula to be evaluated, it must be joined with a concatenation such as "&"
AVERAGEIF Function Examples
So, I recently became some sort of fitness enthusiast largely because of a checkup that happened in my office, and I was told that my BMI was slightly overweight. This was surprising to me because I am quite tall at above 6feet, and with the naked eye no one would ever categorize me as overweight In all, it sparked some sort of fire in me and going forward I started eating healthy and exercising regularly. I didn't feel I was doing enough because I wasn't tracking my progress, so I decided to get a fitness watch and a weighing scale.
Why am I telling you this story? It is because our data set for this section is a list of smartwatches I curated when searching for one to use for my fitness journey. Let' see the list below, then we proceed to using the AVERAGEIF Function to retrieve average values of some criteria.
Double Quotes in Criteria
For this section, we are going to return values for 4 criteria, and all would require double quotes when defining the criteria.
Let's take a look at each of these criteria and the formulas to use
Greater than $200
Our criteria here is to return the average value of fitness watches greater than $200, this means that any watch with a price point lower than $200 would be excluded. We would use the formula below to return this:
Greater than $300
On to the next one, here we need to return the average price of smartwatches above $300. To achieve this, we use the formula below:
Watches running on Zepp OS
Our next task is to return the average price of watches that are running on the Zepp OS. We would tweak the previous formula by removing the logical operator and just typing in the criteria since it is a text value. Then we need to change the range argument to match the column for the OS information which is column D.
Greater than 1 day battery life
Our final example in the double quote section is to return the average price of smartwatches having battery life of more than a day. We would use the formula below to achieve this:
Value from another cell
For the next 3 sections, we would use a different set of criteria for the same data set used in the previous section. Let's take a look at our new set of criteria:
Our first criteria is to return from cell J6 which contains one of the OS listed in column D. Instead of typing in the OS in double quotes like we did in the previous section, we need to select it from cell J6. The formula to use can be seen below.
Specific Number
Next stop is to return the average price of watches that have a 1 day battery life. You would see that we don't need to enclose our criteria in double quotes when it is equal to a number. If it is less than or greater than a number, of course you need to define it with logical operators and enclose both logical operator an number in double quotes. If however the criteria is exactly equal to a number, then you can just type in the number without quotes.
WildCards
Wildcards can be used to average cells with similar features, for example cells having a few characters in common. The Wildcard characters are asterisk (*), question mark (??) and tilde (~). The question mark matches the number of characters in a specified position whilst the asterisk (*) matches any sequence of characters regardless of its length. In our example, we need to return the average price of any OS that has the word droid contained in it. Our formula to achieve this is:
We have the asterisk before and after the word "droid" because it is found somewhere in the middle of the original text "Android Wear OS". If on the other hand it started the text and there are variants of it say droiding OS, droider OS etc, we would need only one asterisks at the end (droid*). Same applies to if we are certain that our wildcard word ends every text which would contain this word.