Average if
 

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

Average if
 

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" wrote in message
...
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))

Average if
 

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_d ata!CJ2:CJ2000)=0),AVERAGE(B2:F2000),"Don't do
it")
--
Jim Cone
Portland, Oregon USA



"T. Valko"
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"

wrote in message
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))

Average if
 

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

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

--
Biff
Microsoft Excel MVP


"Jim Cone" wrote in message
...
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_d ata!CJ2:CJ2000)=0),AVERAGE(B2:F2000),"Don't
do
it")
--
Jim Cone
Portland, Oregon USA



"T. Valko"
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"

wrote in message
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))

Average if
 

Thanks the array is what I was missing.

"T. Valko" wrote:

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" wrote in message
...
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))

Average if
 

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

"T. Valko" wrote:

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" wrote in message
...
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))

Average if
 

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


"mparker" wrote:

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

"T. Valko" wrote:

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" wrote in message
...
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))

Average if
 

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"
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.
--
Biff
Microsoft Excel MVP


"Jim Cone" wrote in message
...
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_d ata!CJ2:CJ2000)=0),AVERAGE(B2:F2000),"Don't
do
it")
--
Jim Cone
Portland, Oregon USA

Average if
 

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

"Idoia" wrote:

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


"mparker" wrote:

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

"T. Valko" wrote:

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" wrote in message
...
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))