How to use the AVERAGE AVERAGEA AVERAGEIF and AVERAGEIFS functions in Excel?

Показать описание

How to use the AVERAGE AVERAGEA AVERAGEIF and AVERAGEIFS functions in Excel?

Hi my name is Roger Ahuja and I'm going to take a couple of minutes and talk to you about the AVERAGE AVERAGEA AVERAGEIF and AVERAGEIFS functions. So we'll start with the Average function. If I wanted to take the average of all of these prices here, I could just go here put an equal sign, Average and then put in F2 which is down from F and across from 2 and then a colon which specifies a range so I want to do F2 to F11 and that's going to highlight this entire range and then close that and hit enter and I have an average of all those numbers. If you wanted to do just the dogs in this as you can see there are 3 dogs up here and three here, we could do the AVERAGEA function which will let us do F2 to F4 and then a comma and then you can do F6, F7, F8 or I could just do F6:F8 as a range and that will give me averages of those. Another way of getting all the dogs would be to use the AVERAGEIF function. I could do equals AVERAGEIF and then first I need to specify the range that I'm searching for the criteria so that would be we're looking for Dog's in this section here. We're going to do A2 to A11 and for the criteria, we want it to equal Dog and notice I put that in quotes and then the range that we're actually going to average is F2 to F11 and if you notice the number before it was $237.50 when I did it manually and it's the same number now. And you can also do the AVERAGEIFS if you have multiple criteria. So here, there's one difference. In the last one we selected the criteria range first, this function we actually select the range we're going to average out first. So we'll first select F2 to F11 and then let's say that we want all the cat's so we want from A2 to A11 and then we want the criteria to be equal to cat. And then let's say we also only want cats that are less than 30 pounds so we're going to select a second range which would be B2 to B11 and the criteria here is less than 30 and we'll close that and that's the average of the 3 cats 175 150 200 and those are the ones that weigh less than 30 pounds and the average of those 3 numbers is 175 and that's it.