Average across worksheets ignoring zero
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" wrote in message
...
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?
|