# Averageif criteria

1. ## Averageif criteria

Hi All,

I want to average the numbers in column C based on criteria in column B. Example, if the value of any of the cells in cells B2:B35 is between 1.0 and 2.0, then the corresponding cells in column C should be averaged. I hope that makes sense...
I tried this formula, but am getting a "divide by zero" error... ``Please Login or Register  to view this content.``  Register To Reply

2. ## Re: Averageif criteria

Since you are using 2007, try (untested):

=SUMPRODUCT(((B2:B35>=1)-(B2:B35>2)>0)*C2:C35)/SUMPRODUCT(--(((B2:B35>=1)-(B2:B35>2))>0))  Register To Reply

3. ## Re: Averageif criteria

My mistake, I guess the averageifs function also applies to Excel 2007.

See if this works for you,

=AVERAGEIFS(C2:C35,B2:B35,">=1",B2:B35,"<=2")  Register To Reply

4. ## Re: Averageif criteria

That should be AND not OR, ie
B2:B35 > 1.0 AND B2:B35 (in fact you've already typed "and" in your description of the problem
Try

=AVERAGEIFS(C2:C35,B2:B35,">=1",B2:B35,"<=2")  Register To Reply

5. ## Re: Averageif criteria

That does the trick (the second one - I didn't try the first). How can I get the max and min values using the same kind of conditions?  Register To Reply

6. ## Re: Averageif criteria

Try these array formulas**:

=MAX(IF((B2:B35>=1)*(B2:B35<=2),C2:C35))

=MIN(IF((B2:B35>=1)*(B2:B35<=2),C2:C35))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.  Register To Reply