Average ignoring Zeros
 

=AVERAGE(IF(Start:End!C22<0,Start:End!C22,""))

Hi All can anyone tell me what's wrong with the above mentioned formula? I
am pressing CTRl+SHIFT+ENTER.

What I am trying to do here is, take the average of cell C22 from all the
sheets between the two sheets named as Start and End ignoring all the zero
values.

Thanks for any help.
Gary

Average ignoring Zeros
 

You can't use an array of references like that in an IF function.

You'd need to do something like this:

List the sheet names in a range of cells:

H1 = Start
H2 = Sheet2
H3 = End

=SUM(Start:End!C22)/SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H3&"'!C22"),"< 0"))

Biff

"Gary" wrote in message
...
=AVERAGE(IF(Start:End!C22<0,Start:End!C22,""))

Hi All can anyone tell me what's wrong with the above mentioned formula? I
am pressing CTRl+SHIFT+ENTER.

What I am trying to do here is, take the average of cell C22 from all the
sheets between the two sheets named as Start and End ignoring all the zero
values.

Thanks for any help.
Gary

Average ignoring Zeros
 

Thanks Biff

The data is in %age. Do you think that would make some difference? because i
think it is.


"T. Valko" wrote in message
...
You can't use an array of references like that in an IF function.

You'd need to do something like this:

List the sheet names in a range of cells:

H1 = Start
H2 = Sheet2
H3 = End

=SUM(Start:End!C22)/SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H3&"'!C22"),"< 0"))

Biff

"Gary" wrote in message
...
=AVERAGE(IF(Start:End!C22<0,Start:End!C22,""))

Hi All can anyone tell me what's wrong with the above mentioned formula?
I am pressing CTRl+SHIFT+ENTER.

What I am trying to do here is, take the average of cell C22 from all the
sheets between the two sheets named as Start and End ignoring all the
zero values.

Thanks for any help.
Gary

Average ignoring Zeros
 

That should not make any difference. A % is still a number.

Biff

"Gary" wrote in message
...
Thanks Biff

The data is in %age. Do you think that would make some difference? because
i think it is.


"T. Valko" wrote in message
...
You can't use an array of references like that in an IF function.

You'd need to do something like this:

List the sheet names in a range of cells:

H1 = Start
H2 = Sheet2
H3 = End

=SUM(Start:End!C22)/SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H3&"'!C22"),"< 0"))

Biff

"Gary" wrote in message
...
=AVERAGE(IF(Start:End!C22<0,Start:End!C22,""))

Hi All can anyone tell me what's wrong with the above mentioned formula?
I am pressing CTRl+SHIFT+ENTER.

What I am trying to do here is, take the average of cell C22 from all
the sheets between the two sheets named as Start and End ignoring all
the zero values.

Thanks for any help.
Gary