Average if - data on separate sheet
 

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

Average if - data on separate sheet
 

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, D2:D2000 ...




On 21 Kwi, 13:09, mparker wrote:
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))

Average if - data on separate sheet
 

In article ,
mparker wrote:

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.

--
Domenic
http://www.xl-central.com

Average if - data on separate sheet
 

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

On 21 Kwi, 13:38, Jarek Kujawa wrote:
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, D2:D2000 ...

On 21 Kwi, 13:09, mparker wrote:



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))- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -