SUMPRODUCT() challenge: Spanning multiple worksheets

[DKowalski]

Hello.

I am trying to calculate a weighted average from 2 sets of data: 1 set is an array in a worksheet, and the 2nd set is a range of values that share a specific location across multiple worksheets.

I was trying to use the following (example) formula to calculate the weighted average from these datasets:
= SUMPRODUCT(B4:B6, C4:C6) / SUM(C4:C6)
where the range B4:B6 is the array of values that spans multiple worksheets, and
where C4:C6 is the array of weights on a single worksheet.

I have just learnt that SUMPRODUCT can only handle arrays, and that an array can NOT span multiple worksheets. As such, when B4:B6 was replaced with 'Weather Ticker:Weather Ticker (10)'!M7 , a #REF! error was generated.

As there is considerable data volume on each worksheet, I'd like to know if I can use a workaround or an alternative to get the weighted average (as described above) WITHOUT reorganising my data layout.

Thanks for your help.....

 

[DKowalski]

Just learned that I have accidentally cross-posted this issue, at http://www.ozgrid.com/forum/showthread.php?t=156827 and also
http://www.excelforum.com/excel-wor...t-challenge-spanning-multiple-worksheets.html
Please forgive.

 

[DKowalski]

accidentally "cross posted", please post replies at http://www.excelforum.com/excel-wor...t-challenge-spanning-multiple-worksheets.html