Weighted Average across worksheets
 

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

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

Weighted Average across worksheets
 

Thanks for that, works fine as an array formula.
As an extension to the problem, I also want to do the same for the cells K2
and M2 on each sheet.
Can you do that for non contiguous cells?

Allan

"Lori" wrote in message
s.com...
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

Weighted Average across worksheets
 

For non-contiguous cells, you could try instead:

=SUMPRODUCT(N(INDIRECT("'"&sheets&"'!K2")),N(INDIR ECT("'"&sheets&"'!K2")))

(divided by the sum.)

AS wrote:
Thanks for that, works fine as an array formula.
As an extension to the problem, I also want to do the same for the cells K2
and M2 on each sheet.
Can you do that for non contiguous cells?

Allan

"Lori" wrote in message
s.com...
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

Weighted Average across worksheets
 

It took me a while to understand it ........ one of the K2 references should
be M2
Changed that and works great.
I prefer this solution whether contiguous or not because it seems to work as
an ordinary formula as well as an array entry formula.
Don't pretend to understand the intricacies !!
Thanks again
Allan


"Lori" wrote in message
oups.com...
For non-contiguous cells, you could try instead:

=SUMPRODUCT(N(INDIRECT("'"&sheets&"'!K2")),N(INDIR ECT("'"&sheets&"'!K2")))

(divided by the sum.)

AS wrote:
Thanks for that, works fine as an array formula.
As an extension to the problem, I also want to do the same for the cells
K2
and M2 on each sheet.
Can you do that for non contiguous cells?

Allan

"Lori" wrote in message
s.com...
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

Weighted Average across worksheets
 

Thanks for the reply, glad it works and you corrected the typo.

AS wrote:
It took me a while to understand it ........ one of the K2 references should
be M2
Changed that and works great.
I prefer this solution whether contiguous or not because it seems to work as
an ordinary formula as well as an array entry formula.
Don't pretend to understand the intricacies !!
Thanks again
Allan


"Lori" wrote in message
oups.com...
For non-contiguous cells, you could try instead:

=SUMPRODUCT(N(INDIRECT("'"&sheets&"'!K2")),N(INDIR ECT("'"&sheets&"'!K2")))

(divided by the sum.)

AS wrote:
Thanks for that, works fine as an array formula.
As an extension to the problem, I also want to do the same for the cells
K2
and M2 on each sheet.
Can you do that for non contiguous cells?

Allan

"Lori" wrote in message
s.com...
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