| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct for multiple sheets
I am trying to create a weighted average on one sheet calculated from
several other sheets. I need to use the product of just two cells on each sheet for the weighted average. I know I cant use sumproduct in 3D, is there another way around this problem? Thanks, Sarah |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct for multiple sheets
=SUMPRODUCT(N(INDIRECT("Sheet"&ROW(1:3)&"!A1")),N( INDIRECT("Sheet"&ROW(1:3)&"!B1")))
-- --- HTH Bob (change the xxxx to gmail if mailing direct) wrote in message oups.com... I am trying to create a weighted average on one sheet calculated from several other sheets. I need to use the product of just two cells on each sheet for the weighted average. I know I cant use sumproduct in 3D, is there another way around this problem? Thanks, Sarah |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct for multiple sheets
HI Bob,
Thank you for the prompt reply, I am still having some trouble and would really appreciate some help. I can seem to figure out how to properly apply the formula. For "Sheet" do input the series of sheets that I would be gathering data from or is this just code? (1:3) is this for the sheet names? A1 and B1 are these the cells I will be using as a source for the data? Is N determined by the data source or do I need to identify it? =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(1:3)&"!A1")),N( INDIRECT("Sheet"&ROW(1:3)¬&"!B1"))) Basically I have several sheets KState, Michigan, NYU, UCLA and each has a number of employees (5,6,7,8) and the mean salary(85000, 90000,75000, 80000), I would like to get the weighted mean of these on a separate sheet. Any further help would be greatly appreciated. Thanks again, Sarah On Jan 25, 11:28 am, "Bob Phillips" wrote: =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(1:3)&"!A1")),N( INDIRECT("Sheet"&ROW(1:3)*&"!B1"))) -- --- HTH Bob (change the xxxx to gmail if mailing direct) wrote in ooglegroups.com... I am trying to create a weighted average on one sheet calculated from several other sheets. I need to use the product of just two cells on each sheet for the weighted average. I know I cant use sumproduct in 3D, is there another way around this problem? Thanks, Sarah- Hide quoted text -- Show quoted text - |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct for multiple sheets
You can place the sheet names in an array of cells. In the following
formula I assume 8 sheet names are in cells K1:K8. Then you can use the following variant of Bob's formula: =SUMPRODUCT(N(INDIRECT(K1:K8&"!A1")),N(INDIRECT(K1 :K8&"!B1"))) HTH Kostis Vezerides On Jan 25, 8:59 pm, wrote: HI Bob, Thank you for the prompt reply, I am still having some trouble and would really appreciate some help. I can seem to figure out how to properly apply the formula. For "Sheet" do input the series of sheets that I would be gathering data from or is this just code? (1:3) is this for the sheet names? A1 and B1 are these the cells I will be using as a source for the data? Is N determined by the data source or do I need to identify it? =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(1:3)&"!A1")),N( INDIRECT("Sheet"&ROW(1:3)¬&"!B1"))) Basically I have several sheets KState, Michigan, NYU, UCLA and each has a number of employees (5,6,7,8) and the mean salary(85000, 90000,75000, 80000), I would like to get the weighted mean of these on a separate sheet. Any further help would be greatly appreciated. Thanks again, Sarah On Jan 25, 11:28 am, "Bob Phillips" wrote: =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(1:3)&"!A1")),N( INDIRECT("Sheet"&ROW(1:3)*&"!B1"))) -- --- HTH Bob (change the xxxx to gmail if mailing direct) wrote in ooglegroups.com... I am trying to create a weighted average on one sheet calculated from several other sheets. I need to use the product of just two cells on each sheet for the weighted average. I know I cant use sumproduct in 3D, is there another way around this problem? Thanks, Sarah- Hide quoted text -- Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct for multiple sheets
You can even use a constant array
=SUMPRODUCT(N(INDIRECT({"KState","Michigan,"NYU"," UCLA"}&"!A1")),N(INDIRECT(({"KState","Michigan,"NY U","UCLA"}&"!B1"))) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "vezerid" wrote in message oups.com... You can place the sheet names in an array of cells. In the following formula I assume 8 sheet names are in cells K1:K8. Then you can use the following variant of Bob's formula: =SUMPRODUCT(N(INDIRECT(K1:K8&"!A1")),N(INDIRECT(K1 :K8&"!B1"))) HTH Kostis Vezerides On Jan 25, 8:59 pm, wrote: HI Bob, Thank you for the prompt reply, I am still having some trouble and would really appreciate some help. I can seem to figure out how to properly apply the formula. For "Sheet" do input the series of sheets that I would be gathering data from or is this just code? (1:3) is this for the sheet names? A1 and B1 are these the cells I will be using as a source for the data? Is N determined by the data source or do I need to identify it? =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(1:3)&"!A1")),N( INDIRECT("Sheet"&ROW(1:3)¬&"!B1"))) Basically I have several sheets KState, Michigan, NYU, UCLA and each has a number of employees (5,6,7,8) and the mean salary(85000, 90000,75000, 80000), I would like to get the weighted mean of these on a separate sheet. Any further help would be greatly appreciated. Thanks again, Sarah On Jan 25, 11:28 am, "Bob Phillips" wrote: =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(1:3)&"!A1")),N( INDIRECT("Sheet"&ROW(1:3)*&"!B1"))) -- --- HTH Bob (change the xxxx to gmail if mailing direct) wrote in ooglegroups.com... I am trying to create a weighted average on one sheet calculated from several other sheets. I need to use the product of just two cells on each sheet for the weighted average. I know I cant use sumproduct in 3D, is there another way around this problem? Thanks, Sarah- Hide quoted text -- Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct for multiple sheets
Hi Bob! (long time...)
I presume the formula runs faster with array constants? And maybe intermediate with a named constant? Good idea for testing later tonight at home... Good night Kostis On Jan 25, 10:08 pm, "Bob Phillips" wrote: You can even use a constant array =SUMPRODUCT(N(INDIRECT({"KState","Michigan,"NYU"," UCLA"}&"!A1")),N(INDIRECT(({"KState","Michigan,"NY U","UCLA"}&"!B1"))) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "vezerid" wrote in ooglegroups.com... You can place the sheet names in an array of cells. In the following formula I assume 8 sheet names are in cells K1:K8. Then you can use the following variant of Bob's formula: =SUMPRODUCT(N(INDIRECT(K1:K8&"!A1")),N(INDIRECT(K1 :K8&"!B1"))) HTH Kostis Vezerides On Jan 25, 8:59 pm, wrote: HI Bob, Thank you for the prompt reply, I am still having some trouble and would really appreciate some help. I can seem to figure out how to properly apply the formula. For "Sheet" do input the series of sheets that I would be gathering data from or is this just code? (1:3) is this for the sheet names? A1 and B1 are these the cells I will be using as a source for the data? Is N determined by the data source or do I need to identify it? =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(1:3)&"!A1")),N( INDIRECT("Sheet"&ROW(1:3)¬&"!B1"))) Basically I have several sheets KState, Michigan, NYU, UCLA and each has a number of employees (5,6,7,8) and the mean salary(85000, 90000,75000, 80000), I would like to get the weighted mean of these on a separate sheet. Any further help would be greatly appreciated. Thanks again, Sarah On Jan 25, 11:28 am, "Bob Phillips" wrote: =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(1:3)&"!A1")),N( INDIRECT("Sheet"&ROW(1:3)*&"!B1"))) -- --- HTH Bob (change the xxxx to gmail if mailing direct) wrote in ooglegroups.com... I am trying to create a weighted average on one sheet calculated from several other sheets. I need to use the product of just two cells on each sheet for the weighted average. I know I cant use sumproduct in 3D, is there another way around this problem? Thanks, Sarah- Hide quoted text -- Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct for multiple sheets
Hi Kostis,
How are you? I bet it's nicer where you are than here. 0C, but feels like -4. I would have thought it would be quicker if the list is in cells rather than inline constant, less evaluating to be done. And more maintainable, but not as easily readable.Your results should be interesting. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "vezerid" wrote in message ups.com... Hi Bob! (long time...) I presume the formula runs faster with array constants? And maybe intermediate with a named constant? Good idea for testing later tonight at home... Good night Kostis On Jan 25, 10:08 pm, "Bob Phillips" wrote: You can even use a constant array =SUMPRODUCT(N(INDIRECT({"KState","Michigan,"NYU"," UCLA"}&"!A1")),N(INDIRECT(({"KState","Michigan,"NY U","UCLA"}&"!B1"))) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "vezerid" wrote in ooglegroups.com... You can place the sheet names in an array of cells. In the following formula I assume 8 sheet names are in cells K1:K8. Then you can use the following variant of Bob's formula: =SUMPRODUCT(N(INDIRECT(K1:K8&"!A1")),N(INDIRECT(K1 :K8&"!B1"))) HTH Kostis Vezerides On Jan 25, 8:59 pm, wrote: HI Bob, Thank you for the prompt reply, I am still having some trouble and would really appreciate some help. I can seem to figure out how to properly apply the formula. For "Sheet" do input the series of sheets that I would be gathering data from or is this just code? (1:3) is this for the sheet names? A1 and B1 are these the cells I will be using as a source for the data? Is N determined by the data source or do I need to identify it? =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(1:3)&"!A1")),N( INDIRECT("Sheet"&ROW(1:3)¬&"!B1"))) Basically I have several sheets KState, Michigan, NYU, UCLA and each has a number of employees (5,6,7,8) and the mean salary(85000, 90000,75000, 80000), I would like to get the weighted mean of these on a separate sheet. Any further help would be greatly appreciated. Thanks again, Sarah On Jan 25, 11:28 am, "Bob Phillips" wrote: =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(1:3)&"!A1")),N( INDIRECT("Sheet"&ROW(1:3)*&"!B1"))) -- --- HTH Bob (change the xxxx to gmail if mailing direct) wrote in ooglegroups.com... I am trying to create a weighted average on one sheet calculated from several other sheets. I need to use the product of just two cells on each sheet for the weighted average. I know I cant use sumproduct in 3D, is there another way around this problem? Thanks, Sarah- Hide quoted text -- Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Consolidating data from multiple sheets. | Excel Discussion (Misc queries) | |||
| adding like items from different sheets | Excel Worksheet Functions | |||
| insert Rows with Formulas in Place on Multiple Sheets? | Excel Discussion (Misc queries) | |||
| Sumproduct accross multiple sheets | Excel Discussion (Misc queries) | |||
| calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
Linear Mode
