Average across worksheets ignoring zero

[Ted]

I am using =AVERAGE(Start:End!F37) to calculate an average across multiple
worksheets. However, some of the sheets contain a zero in cell B37. I need
to ignore that zero (and that sheet) entirely. Can anyone help?

[T. Valko]

>I am using =AVERAGE(Start:End!F37)

>some of the sheets contain a zero in cell B37

Which cell is it, B37 or F37?

A single formula to do this can be really complicated (especially if you
need an error trap).

You would be better off using a formula on each individual sheet that checks
for 0.

On each sheet in say, cell X1:

=IF(F37=0,"",F37)

Then you can still use the much simpler:

=AVERAGE(Start:End!X1)

--
Biff
Microsoft Excel MVP

"Ted" <T...@discussions.microsoft.com> wrote in message
news:D351C6D5-A1BF-400C...@microsoft.com...

[Jacob Skaria]

One way by specifying the sheet names..
=SUMPRODUCT(SUMIF(INDIRECT("'"&{"sheet1","sheet2"}&"'!F37"),"<>0"))/
SUMPRODUCT(COUNTIF(INDIRECT("'"&{"sheet1","sheet2"}&"'!F37"),"<>0"))

'OR having the sheet names in J1:J3 (no blank cells)
=SUMPRODUCT(SUMIF(INDIRECT("'"& J1:J3 &"'!F37"),"<>0"))/
SUMPRODUCT(COUNTIF(INDIRECT("'"& J1:J3 &"'!F37"),"<>0"))

If this post helps click Yes
---------------
Jacob Skaria