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
 

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

Copy a range data from 1 sheet to multiple selected sheets 1
Max of multiple fields 2
Average Non-consecutive Cells 4
Problem with a Sumproduct formula 3
Pivot table headache 2
Average multiple colums 5
Calculate statistical median of multiple fields in a query 1
Goal Seek using Time 2

Top