AVG If w/multiple criteria

  • Thread starter Thread starter Brennen
  • Start date Start date
B

Brennen

I trying to evaluate some survey responses and would
appreciate some help.

Col A: 1,1,0,1,0,1
Col B: 2,3,5,6,3,blank

I want to get the AVG of Col B when the value of Col A =
1 AND the value of Col B is 1,2,3,4 or 5 (ignore blanks
and >5).

Thanks in advance!
 
Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF(A1:A100=1,B1:B100))
blank cells are automatically ignored by AVERAGE
 
To include the blank space I would say do this:
=SUMIF(A1:A6,1,B1:B6)/COUNTIF(A1:A6,1)
However, the product is 2.75 whereas the average of {2,3,6} is 3.666667 so
bear that in mind.
 
Thanks for the quick response.

That formula won't capture that I only want to average
the cells in Col B that = 1-5 (I want to ignore higher
values, will it?
 
=AVERAGE(IF((A2:A7)*ISNUMBER(MATCH(B2:B7,{1,2,3,4,5},0)),B2:B7))

which must be confirmed with control+shift+enter instead of just enter. You
can also subtitute a range for {1,2,3,4,5}, which houses the relevant
numbers.
 
Brennen

Enter as an array with Ctrl|Shift|Enter
=AVERAGE(IF(A2:A100=1,IF(B2:B100>0,IF(B2:B100<5,B2:B100))))

--
XL2002
Regards

William

(e-mail address removed)

| I trying to evaluate some survey responses and would
| appreciate some help.
|
| Col A: 1,1,0,1,0,1
| Col B: 2,3,5,6,3,blank
|
| I want to get the AVG of Col B when the value of Col A =
| 1 AND the value of Col B is 1,2,3,4 or 5 (ignore blanks
| and >5).
|
| Thanks in advance!
|
|
 
Back
Top