Average if

[mparker]

I am trying to create an average of a range of cells - B2:F2000 - but only if
Column CJ is 1. I am also trying to inset this equation to a different
worksheet. The sheet with the data in it is called paste_data. Here is the
equation I am trying to use but it results in an error.

=AVERAGE(IF(paste_data!CJ2:CJ2000=1,paste_data!B2:F2000))

 

[T. Valko]

There's nothing wrong with the formula. Did you enter it as an array? What
specific error do you get?

 

[Jim Cone]

Biff,
I get 0 as a result when entered as an array formula.
However, this 'meat and potatoes' version seems to work...
=IF(AND(MAX(paste_data!CJ2:CJ2000) =1,MIN(paste_data!CJ2:CJ2000)=1,COUNTBLANK(paste_data!CJ2:CJ2000)=0),AVERAGE(B2:F2000),"Don't do
it")
--
Jim Cone
Portland, Oregon USA



"T. Valko" <[email protected]>
wrote in message
There's nothing wrong with the formula. Did you enter it as an array? What
specific error do you get?
--
Biff
Microsoft Excel MVP



"mparker"

 

[T. Valko]

=AVERAGE(IF(Sheet1!CJ2:CJ2000=1,Sheet1!B2:F2000))

Array entered, worked just fine for me. Confirmation formulas also verified
the result was correct.

 

[mparker]

Thanks the array is what I was missing.

There's nothing wrong with the formula. Did you enter it as an array? What
specific error do you get?

 

[mparker]

Just one more questions, this equation seems to be calculating blanks as 0s
in the average. Any idea?

 

[Idoia]

=AVERAGE(IF((paste_data!CJ2:CJ2000=1)*(paste_data!B2:F2000<>""),paste_data!B2:F2000))

 

[Jim Cone]

Biff,
It appears that the elephant I saw was not the elephant you saw...
I saw the issue as: only average the data in B2:F2000 if every
cell in CJ2:CJ2000 has a 1 in it.
--
Jim Cone
Portland, Oregon USA



"T. Valko" <[email protected]>
wrote in message
=AVERAGE(IF(Sheet1!CJ2:CJ2000=1,Sheet1!B2:F2000))
Array entered, worked just fine for me. Confirmation formulas also verified
the result was correct.

 

[mparker]

Anyway to change the B2 to C2 instead of changing it to B3?