AVERAGEIFS Function of excel helps us to find out the average of specified values. By specified values, we mean those values that come under a particular category.
For example, there are many colleges out there which have the admission criteria of 75% in bachelors and cut-off in CAT 85%.
As we are aware of the number of students who sit for CAT exam, through AVERAGEIFS Function we can the average of all those students who are as per the criteria.
To understand it in a more detailed manner, here’s an example for the same:
As you can observe in the image above, we have taken a shoe manufacturing company’s example. We have mentioned the product code which is unique to every product, its type for whether it’s a sneaker or a formal attire shoe, and its size along with the monthly sale.
Let’s dig in by inputting the formula:
The formula for the same is
As you can observe in the image above, the following formula expects the following:
Average range- this is the range consisting of values from which you need your average to be taken out.
So, as we want our average to be taken out from the monthly sales, so we will select the same.
After the same, comes the criteria range 1- You see, the only difference between the function AVERAGEIF & AVERAGEIFS is that AVERAGEIF only lets you take one criteria range but on the other hand you can take as many criteria ranges as you want through AVERAGEIFS Function.
So, our criteria range 1is the shoe type.
After selecting the same, we come to the point of criteria 1.
You see, first we selected the criteria range, i.e. the whole range now we get to make a choice. Its basically like information in detail.
So, let’s say that we need information about sneaker shoes and not formal shoes, so we will type the same.
Please note that you cannot make any spelling error. For example, if it is written sneaker in your data entry then you have to write the same, you cannot write it as SNEAKER or sneakers.
Then comes criteria range 2- which is our shoe size.
After selecting the same, jot down the criteria 2.
We want the average of those shoe products which have the shoe size greater than 6.
Kindly remember that if you want to include the shoe size 6 as well, then you need to jot it down as:
Now, press “Enter”.
And BAMMM! Your average value of shoe products which are sneaker as well as have the shoe size greater than 6 is 7385.714.
*Do not forget to write the criteria in quotation mark as given in the images. Again criteria and not criteria range.
It’s easy, isn’t it?