Calculate Average Accross Multiple Worksheets

G

Guest

I'm trying to calculate an average accross multiple worksheets. I could pull
all values into the final worksheet, but there must be a way to do this
without having hidden data...

Sheets / Cells
'Q1'!D35:'Q2'!D35:'Q3'!D35:'Q4'!D35

Four Worksheets: Q1, Q2, Q3 & Q4
Four Cells: D35, D35, D35 & D35

I also want to prevent a #DIV error if the values are = 0

This is the formula I have, I just can't seem to make it work:
=IF(ISERROR(SUMPRODUCT('Q1'!D35+'Q2'!D35+'Q3'!D35+'Q4'!D35)/SUMPRODUCT(('Q1'!D35+'Q2'!D35+'Q3'!D35+'Q4'!D35>0)*(ISNUMBER('Q1'!D35+'Q2'!D35+'Q3'!D35+'Q4'!D35)))),"",SUMPRODUCT('Q1'!D35+'Q2'!D35+'Q3'!D35+'Q4'!D35)/SUMPRODUCT(('Q1'!D35+'Q2'!D35+'Q3'!D35+'Q4'!D35>0)*(ISNUMBER('Q1'!D35+'Q2'!D35+'Q3'!D35+'Q4'!D35))))

Also, (not sure if this matters) all four cells in all four sheets are
average calculations themselves. I'm calculating an average in each
worksheet, and then I want to calculate a total average in a summary sheet.

Any help would be appreciated!

Jason

G

Guest

=SUM(Q1:Q4'!D35)/4
there should be no possibility of divide by zero