Average ignoring Zeros

[Gary]

=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

 

[T. Valko]

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]

Thanks Biff

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

 

[T. Valko]

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

Biff