Average if

  • Thread starter Thread starter mparker
  • Start date Start date
M

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))
 
There's nothing wrong with the formula. Did you enter it as an array? What
specific error do you get?
 
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"
 
=AVERAGE(IF(Sheet1!CJ2:CJ2000=1,Sheet1!B2:F2000))

Array entered, worked just fine for me. Confirmation formulas also verified
the result was correct.
 
Just one more questions, this equation seems to be calculating blanks as 0s
in the average. Any idea?
 
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.
 
Back
Top