| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Average across worksheets | Excel Worksheet Functions | |||
| How do I weighted average using Pivot Tables? | Charts and Charting in Excel | |||
| weighted average formular | Excel Worksheet Functions | |||
| weighted average | Excel Discussion (Misc queries) | |||
| Show weighted average value after filter. | Excel Worksheet Functions | |||
Linear Mode
