Average if - data on separate sheet

[mparker]

I am trying to calculate the average of 5 questions if another question
responded 1 or 2, this equation keeps bringing up 0. The data is on another
worksheet in my workbook. The worksheet is called paste_data.


=AVERAGE(IF((paste_data!CL2:CL2000=1 or 2),paste_data!B2:F2000))

 

[Jarek Kujawa]

Excel will not calculate this formula for B2:F2000

for B2:B2000 try:

=AVERAGE(IF(((paste_data!CL2:CL2000=1)+(paste_data!
CL2:CL2000=2)),paste_data!B2:B2000)))

CTRL+SHIFT+ENTER this formula as it is an array-formula. If you insert
it correctly curly brackets { } will show

then you may continue for other ranges C2:C2000, D22000 ...

 

[Domenic]

I am trying to calculate the average of 5 questions if another question
responded 1 or 2, this equation keeps bringing up 0. The data is on another
worksheet in my workbook. The worksheet is called paste_data.


=AVERAGE(IF((paste_data!CL2:CL2000=1 or 2),paste_data!B2:F2000))

To average B2:F2000, where the corresponding value in CL2:CL2000 is
either 1 or 2, try...

=AVERAGE(IF(ISNUMBER(MATCH(paste_data!CL2:CL2000,{1,2},0)),paste_data!B2:
F2000))

....confirmed with CONTROL+SHIFT+ENTER.

 

[Jarek Kujawa]

ooops, sorry, I was mistaken

=AVERAGE(IF(((paste_data!CL2:CL2000=1)+(paste_data!
CL2:CL2000=2)),paste_data!B2:F2000)))

WILL work

CTRL+SHIFT+ENTER this formula