A while back I wrote a post about the SUMIFS function. This blog post is about the AVERAGEIFS function. It’s slightly different to the regular AVERAGEIF function and I will describe the functionality behind it. In the end I will describe how you can use the AVERAGEIFS function using a reference to a data validation field. This provides a combo box list option which makes the function dynamic.
First things first
I know that everything I write about here can be done by filtering the dataset. The choice is entirely up to you. I like using the functions so I don’t have to filter my data constantly. If you want to join create the list with sample data as shown below. I have assigned named ranges to the various columns. This makes the function more readable. So I have named the first colored column “product” (which isn’t a very good name because it collides with the existing function name!). The second column is called “size” and the third column is called “color”. Finally the last column is called “price”. Of course you have the option just to select the various ranges like this “A2:A15”. I am a fan of naming ranges because I think that “Size” is easier to read than “B2:B15”.
What we are trying to achieve
What we want to achieve is the average price range based on criteria we supply using the data. So in this case we are looking for three sets of answers.
- What’s the average price per product
- What’s the average price per product per size
- What’s the average price per product per size per color
The average price per product can be calculated by using the regular AVERAGEIF function. In the cells next to the list with data I added the following data so we have a place to get our answer:
Now let’s have a look at the first function. It states =AVERAGEIF(product, “Jeans”, Price). In this case we just want to see the average of either the Jeans or the Shirts. Because we have only one criteria so we can use AVERAGEIF here. We need to use the IF because we have more than one variable (Jeans, shirts). The order of arguments of the function is important when working with the AVERAGEIF or AVERAGEIFS functions. So let’s see if we can spot the difference: First we have the AVERAGEIF function. This function first wants to know the range where to look for the criteria. The next criteria is to look for what in that specific range (your criteria) and finally you select the column on which Excel should calculate the average.
This is what the AVERAGEIFS function looks like:
Now here’s the hard part. In contrast to the AVERAGIF function this function starts with the range that needs to be averaged (the last argument in the AVERAGEIF function), the second argument is the range we are going to use to look for the specified value and finally you provide the specific value you are looking for in the previously set range.
So if we want to know the average price for Jeans we use the following:
=AVERAGEIF(product, “Jeans”, price) which reads as follows
Look in the product range for the criteria “jeans” and look in the price column. From all the records you have found show me the average.
Now for the AVERAGEIFS function which we use to get an answer to the question:
Show me the average price for the product “Jeans” with the size “s”.
The hard part is the difference in order in comparison to the regular AVERAGEIF. The AVERAGEIFS version starts with the range that needs to be averaged and after that you set the various criteria. In our example this leads to the following AVERAGEIFS function:
Which translates like show me the average price for the product “jeans” where the size is “s”.
By the way it seems that Excel doesn’t mind if you use capitalized letters in your criteria!
In our last sample we see that the question is as follows:
Show me average per product per size per color. So we have an additional criteria set. For the AVERAGEIFS function that makes no difference. Look at the picture and see what function has been entered to tackle this question.
Adding a bit of functionality using data validation
Using the AVERAGEIFS function this way can be tedious because I do have to change the criteria used in the function to see other values when for example the product is Shirt instead of Jeans. To tackle this problem I decided to add data validation to cells. This way I can use comboboxes to make my choices without having to change mu function. Let’s see how this is done:
and now we come to the nice part of adding this combo box to the AVERAGEIFS function.
Instead of typing the value “Jeans” or “Shirt” we just refer to the cell which contains the data validation and the choice we have made in that cell!
So look at the image above and see that the AVERAGEIFS function is not looking at “Jeans” or “Shirt” anymore but instead it is looking at a cell reference which contains our drop down combo boxes. What ever you choose will be the variable for the AVERAGEIFS function.
But wait… what if it goes wrong?
Everyone who works with Excel knows that you might get the famous #DIV/0 when something is not supplied or is not available. Most of them just leave that and will carry on. I am the person who likes to provide a little error handling just in case. So to finish the function of properly I embedded the whole AVERAGEIFS function in the IFERROR function. Now when a certain combination is not possible just because the option is not there you will see “Not Available” instead of #DIV/0! which looks better in my opinion!
And here is what the end result might look like:
Every time you change the combo box value to a different setting the function will recalculate and show you the outcome for that specific combination. How cool is that!