| 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 - |
| 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 | |||
Hybrid Mode
