Help with this formula

G

Guest

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?
 
B

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
 

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

Similar Threads

Need to create a macro that does this..... 1
Multiple If Statements 3
formula to look at current date and markdown date to give price 4
Subtotal and IF Formula 1
Changing plot order in series formulas 3
problem summing columns with dates 2
automating an array 1
Calculate difference in dates 11

Top