Help with this formula
 

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?

Help with this formula
 

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" wrote in message
...
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?