Weighted Average across worksheets
Sumproduct cannot be used with 3D-references. Try instead:
=SUM(SUBTOTAL(6,INDIRECT("'"&sheets&"'!K2:L2"))) / SUM('P1:P12'!K2)
where "sheets" refers to a range of cells containing the sheet names:
P1,P2,P3,...
AS wrote:
I am aware of the technique using sumproduct to calculate a weighted average
figure, but this doesn't appear to work across worksheets.
I have 12 worksheets named P1 - P12 and am trying to calculate a weighted
average of the cells K2 and L2 on each sheet
The formula =SUMPRODUCT('P1:P12'!L2,'P1:P12'!K2) / SUM('P1:P12'!K2) returns
#REF!
Can anyone help please.
Allan
|