| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate Average Accross Multiple Worksheets
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'!D350) *(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'!D350) *(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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate Average Accross Multiple Worksheets
=SUM(Q1:Q4'!D35)/4
there should be no possibility of divide by zero -- Gary''s Student - gsnu200718 "JK" wrote: 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'!D350) *(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'!D350) *(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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding matching fields accross multiple worksheets | Excel Discussion (Misc queries) | |||
| 1 cell average across multiple worksheets | Excel Worksheet Functions | |||
| Vlookup accross multiple worksheets | Excel Worksheet Functions | |||
| CALCULATE AVERAGE BETWEEN WORKSHEETS | Excel Discussion (Misc queries) | |||
| CALCULATE AVERAGE BETWEEN WORKSHEETS | Excel Discussion (Misc queries) | |||
Linear Mode
