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.
Last edited by wpryan; 11-03-2015 at 06:55 AM.
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))
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")
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")
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
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?
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.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks