AVERAGEIF - too many arguments

[Swanson]

Hello,

My formula below is giving me the message I have too many arguments...Is there a better formula to use if I have a specific criteria that needs to be met and I want the average of values that are greater than 150 and less than 500

=AVERAGEIF('CAR Data'!I235:I287,$J15,">=150",'CAR Data'!M235:M287,"<=500")

 

[RoryA]

Welcome to the forum.

What is the $J15 bit doing there? It looks like perhaps you should be using AVERAGEIFS like this:

=AVERAGEIFS('CAR Data'!M235:M287,'CAR Data'!I235:I287,$J15,'CAR Data'!M235:M287,">=150",'CAR Data'!M235:M287,"<=500")

 

[Swanson]

lol I was trying to be specific about the criteria to reference. Does it look werid?

'CAR Data'!I235:I287 - range of names some listed multiple times
$J15 - the specific name I wanted to reference. Added the $ so I can pull down the formula
">=150",'CAR Data'!M235:M287,"<=500" average value that are greater than 150 and less than 500

I noticed AVERAGEIFS took but i was getting a value error. let me try your with your help!

 

[Swanson]

it worked. I am getting a #DIV/0 error. I didn't realize with AVERAGEIFS it needs to include both column M and I in the first range

 

[RoryA]

You don't.
Column M is the first argument - the range you want the average of.
Column I is the first criteria range, and J15 is the criterion.
Then you have Column M twice as additional criteria ranges because you only want values >=150 and <=500.

So there are 7 arguments in all.

#DIV/0 means that no values match your criteria.

 

[Swanson]

Big help, thank you!