|
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 |
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 |
| All times are GMT +1. The time now is 11:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com