# 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...

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))

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")

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")

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?

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1