Help with this formula

[Darren]

I have the following formula: =AVERAGE('MD 1'!J16,'MD 2'!J8,'MD 3'!J8,'MD
4'!J8,'MD 5'!J8,'MD 6'!J8,'MD 7'!J8,'MD 8'!J8,'MD 9'!J8,'MD 10'!J8,'MD
11'!J8,'MD 12'!J8,'MD 13'!J8,'MD 14'!J8,' MD 15'!J8,'MD 16'!J8,'MD 17'!J8,'MD
18'!J8,'MD 19'!J8,'MD 20'!J8) . It calculates the average from the
appropriate cell value from each of the 20 worksheets. However, how do I
correct the formula so if no data is in a cell, I do not get the #DIV/0 error
message?

[Bernie Deitrick]

Darren,

Move MD 1's J16 to J8, and use the formula

=AVERAGE('MD 1:MD 20'!J8)

This formula will ignore blanks, and only return an error if all the cells are blank.

HTH,
Bernie
MS Excel MVP


"Darren" <Darren@discussions.microsoft.com> wrote in message
news:83432E7D-603D-48CB-93BF-E5294498B0F9@microsoft.com...
>I have the following formula: =AVERAGE('MD 1'!J16,'MD 2'!J8,'MD 3'!J8,'MD
> 4'!J8,'MD 5'!J8,'MD 6'!J8,'MD 7'!J8,'MD 8'!J8,'MD 9'!J8,'MD 10'!J8,'MD
> 11'!J8,'MD 12'!J8,'MD 13'!J8,'MD 14'!J8,' MD 15'!J8,'MD 16'!J8,'MD 17'!J8,'MD
> 18'!J8,'MD 19'!J8,'MD 20'!J8) . It calculates the average from the
> appropriate cell value from each of the 20 worksheets. However, how do I
> correct the formula so if no data is in a cell, I do not get the #DIV/0 error
> message?