Average ignoring Zeros

G

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

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
 
G

Gary

Thanks Biff

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top