Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
??SUMPRODUCT? other solution?
Sheet 1: Sheet 2:
week # of avg Store week of of ad stores sales Region # act. sls act sls Region 1 27 16 ? 1 145 23 77 Region 2 27 7 ? 1 592 23 0 Region 3 27 9 ? 3 106 28 251 The result is need is avg sales by way of: On Sheet 1, IF the Region is named Region 1 AND the Week of Ad is 27, then go to Sheet 2 and sum the data for stores in Region 1 that are in week 23 AND are 0. Take this result and divide by the # of stores on Sheet 1 for a result in avg sales. IF SUM PRODUCT is the solution, I have read about SUMPRODUCT but don't understand it enough to do it from my reading so if you could be so kind as to provide me with a calculation, that would be so appreciated. If SUMPRODUCT is not the solution, other suggestions please? In advance, THANK YOU! jane |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
??SUMPRODUCT? other solution?
Why week of ad 27 gives week of act. sls = 23?
Maybe, but probably not, =SUMPRODUCT(--(Sheet2!A2:A100=Sheet1!A2),--(Sheet2!C2:C100=Sheet1!B2-4),Shee t2!B2:B100)) -- HTH RP (remove nothere from the email address if mailing direct) "Jane" wrote in message ... Sheet 1: Sheet 2: week # of avg Store week of of ad stores sales Region # act. sls act sls Region 1 27 16 ? 1 145 23 77 Region 2 27 7 ? 1 592 23 0 Region 3 27 9 ? 3 106 28 251 The result is need is avg sales by way of: On Sheet 1, IF the Region is named Region 1 AND the Week of Ad is 27, then go to Sheet 2 and sum the data for stores in Region 1 that are in week 23 AND are 0. Take this result and divide by the # of stores on Sheet 1 for a result in avg sales. IF SUM PRODUCT is the solution, I have read about SUMPRODUCT but don't understand it enough to do it from my reading so if you could be so kind as to provide me with a calculation, that would be so appreciated. If SUMPRODUCT is not the solution, other suggestions please? In advance, THANK YOU! jane |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
??SUMPRODUCT? other solution?
1) Please explain:"On Sheet 1, IF the Region is named Region 1 AND the Week
of Ad is 27, then go to Sheet 2 and sum the data for stores in Region 1 that are in week 23" Should both be 27 or 23? 2) Unlikely to be negative sales, so can we ignore "AND are 0." Adding 0 before we divide will not affect average. Looks like a SUMPRODUCT solution is likely. Answer the point about and I'll try. Look here for explanation of SUMPRODUCT: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jane" wrote in message ... Sheet 1: Sheet 2: week # of avg Store week of of ad stores sales Region # act. sls act sls Region 1 27 16 ? 1 145 23 77 Region 2 27 7 ? 1 592 23 0 Region 3 27 9 ? 3 106 28 251 The result is need is avg sales by way of: On Sheet 1, IF the Region is named Region 1 AND the Week of Ad is 27, then go to Sheet 2 and sum the data for stores in Region 1 that are in week 23 AND are 0. Take this result and divide by the # of stores on Sheet 1 for a result in avg sales. IF SUM PRODUCT is the solution, I have read about SUMPRODUCT but don't understand it enough to do it from my reading so if you could be so kind as to provide me with a calculation, that would be so appreciated. If SUMPRODUCT is not the solution, other suggestions please? In advance, THANK YOU! jane |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
??SUMPRODUCT? other solution?
On the assumption that we have 23's and no 27's (or visa versa)
Sheet2: Row 1 used for labels Row 2 has the data: 1 145 23 77 in A2, B2, C2..... Row 3 has the data: 1 592 23 0 etc On Sheet 1 Row 1 used for labels A2 has text: Region 1 (I extract the 1 using RIGHT(A2) hope there are less than 10 regions) B2 has the week of ad, 23 C2 has the # or stores, 16 D2 has the average using: =SUMPRODUCT(--(Sheet2!$A$2:$A$51=--RIGHT(A2)),--(Sheet2!$C$2:$C$51=B2),Sheet2!$D$2:$D$51)/C2 With region 2 in row 3 we can copy this formula down to D3; that is why I made the references absolute. Of course you can change the 51's to the row number that is right for you. Look at like this: a) on the table in sheet2, do we have the right region? The result is a series of 1's and 0's b) on that table, do we have the right week?The result is a series of 1's and 0's c) find all the sales - a series of numbers Now SUMPRODUCT multiples the 1/0 of (a), the 1/0 of (b) and the sales of (c) to give a sum of the sales from the right region in the right week. Think of multiplying by 0 as discarding the sales from wrong region or week. We divide by the number of stores to get an average. Any use to you? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jane" wrote in message ... Sheet 1: Sheet 2: week # of avg Store week of of ad stores sales Region # act. sls act sls Region 1 27 16 ? 1 145 23 77 Region 2 27 7 ? 1 592 23 0 Region 3 27 9 ? 3 106 28 251 The result is need is avg sales by way of: On Sheet 1, IF the Region is named Region 1 AND the Week of Ad is 27, then go to Sheet 2 and sum the data for stores in Region 1 that are in week 23 AND are 0. Take this result and divide by the # of stores on Sheet 1 for a result in avg sales. IF SUM PRODUCT is the solution, I have read about SUMPRODUCT but don't understand it enough to do it from my reading so if you could be so kind as to provide me with a calculation, that would be so appreciated. If SUMPRODUCT is not the solution, other suggestions please? In advance, THANK YOU! jane |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
??SUMPRODUCT? other solution?
I know it seems unlikely but actually I do want the combinatioon of week 27
and week 23... here is why: certain stores have an ad occur in week 27. Thereforem we are tracking the sales in not only weeks 23 but I will need the calculation for weeks 24 thru 32. This will allow us to capture a spike in sales and when it occurs during those weeks. I actually also must restate how the info is arranged on SHeet 2 - here is the correction: Sheet 2 Region Store Week 23 # sales 1 145 77 1 592 0 3 106 251 I hope this clarifies... "Bob Phillips" wrote: Why week of ad 27 gives week of act. sls = 23? Maybe, but probably not, =SUMPRODUCT(--(Sheet2!A2:A100=Sheet1!A2),--(Sheet2!C2:C100=Sheet1!B2-4),Shee t2!B2:B100)) -- HTH RP (remove nothere from the email address if mailing direct) "Jane" wrote in message ... Sheet 1: Sheet 2: week # of avg Store week of of ad stores sales Region # act. sls act sls Region 1 27 16 ? 1 145 23 77 Region 2 27 7 ? 1 592 23 0 Region 3 27 9 ? 3 106 28 251 The result is need is avg sales by way of: On Sheet 1, IF the Region is named Region 1 AND the Week of Ad is 27, then go to Sheet 2 and sum the data for stores in Region 1 that are in week 23 AND are 0. Take this result and divide by the # of stores on Sheet 1 for a result in avg sales. IF SUM PRODUCT is the solution, I have read about SUMPRODUCT but don't understand it enough to do it from my reading so if you could be so kind as to provide me with a calculation, that would be so appreciated. If SUMPRODUCT is not the solution, other suggestions please? In advance, THANK YOU! jane |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
??SUMPRODUCT? other solution?
I know it seems unlikely but actually I do want the combinatioon of week 27
and week 23... here is why: certain stores have an ad occur in week 27. Thereforem we are tracking the sales in not only weeks 23 but I will need the calculation for weeks 24 thru 32. This will allow us to capture a spike in sales and when it occurs during those weeks. I actually also must restate how the info is arranged on SHeet 2 - here is the correction: Sheet 2 Region Store Week 23 # sales 1 145 77 1 592 0 3 106 251 I hope this clarifies... "Bernard Liengme" wrote: 1) Please explain:"On Sheet 1, IF the Region is named Region 1 AND the Week of Ad is 27, then go to Sheet 2 and sum the data for stores in Region 1 that are in week 23" Should both be 27 or 23? 2) Unlikely to be negative sales, so can we ignore "AND are 0." Adding 0 before we divide will not affect average. Looks like a SUMPRODUCT solution is likely. Answer the point about and I'll try. Look here for explanation of SUMPRODUCT: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jane" wrote in message ... Sheet 1: Sheet 2: week # of avg Store week of of ad stores sales Region # act. sls act sls Region 1 27 16 ? 1 145 23 77 Region 2 27 7 ? 1 592 23 0 Region 3 27 9 ? 3 106 28 251 The result is need is avg sales by way of: On Sheet 1, IF the Region is named Region 1 AND the Week of Ad is 27, then go to Sheet 2 and sum the data for stores in Region 1 that are in week 23 AND are 0. Take this result and divide by the # of stores on Sheet 1 for a result in avg sales. IF SUM PRODUCT is the solution, I have read about SUMPRODUCT but don't understand it enough to do it from my reading so if you could be so kind as to provide me with a calculation, that would be so appreciated. If SUMPRODUCT is not the solution, other suggestions please? In advance, THANK YOU! jane |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
??SUMPRODUCT? other solution?
I appreciate your explanation - it has helped me to understand how sumproduct
works... I'm going to take a try but I have another related question - did you see my clarification of Sheet 2? and how do I indicate summing 0 (the step before dividing by the # of stores to get the average)? thank you for your time Bernard! jane "Bernard Liengme" wrote: On the assumption that we have 23's and no 27's (or visa versa) Sheet2: Row 1 used for labels Row 2 has the data: 1 145 23 77 in A2, B2, C2..... Row 3 has the data: 1 592 23 0 etc On Sheet 1 Row 1 used for labels A2 has text: Region 1 (I extract the 1 using RIGHT(A2) hope there are less than 10 regions) B2 has the week of ad, 23 C2 has the # or stores, 16 D2 has the average using: =SUMPRODUCT(--(Sheet2!$A$2:$A$51=--RIGHT(A2)),--(Sheet2!$C$2:$C$51=B2),Sheet2!$D$2:$D$51)/C2 With region 2 in row 3 we can copy this formula down to D3; that is why I made the references absolute. Of course you can change the 51's to the row number that is right for you. Look at like this: a) on the table in sheet2, do we have the right region? The result is a series of 1's and 0's b) on that table, do we have the right week?The result is a series of 1's and 0's c) find all the sales - a series of numbers Now SUMPRODUCT multiples the 1/0 of (a), the 1/0 of (b) and the sales of (c) to give a sum of the sales from the right region in the right week. Think of multiplying by 0 as discarding the sales from wrong region or week. We divide by the number of stores to get an average. Any use to you? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jane" wrote in message ... Sheet 1: Sheet 2: week # of avg Store week of of ad stores sales Region # act. sls act sls Region 1 27 16 ? 1 145 23 77 Region 2 27 7 ? 1 592 23 0 Region 3 27 9 ? 3 106 28 251 The result is need is avg sales by way of: On Sheet 1, IF the Region is named Region 1 AND the Week of Ad is 27, then go to Sheet 2 and sum the data for stores in Region 1 that are in week 23 AND are 0. Take this result and divide by the # of stores on Sheet 1 for a result in avg sales. IF SUM PRODUCT is the solution, I have read about SUMPRODUCT but don't understand it enough to do it from my reading so if you could be so kind as to provide me with a calculation, that would be so appreciated. If SUMPRODUCT is not the solution, other suggestions please? In advance, THANK YOU! jane |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show trial solution | Excel Worksheet Functions | |||
How to deploy an Excel 2003 solution | Excel Discussion (Misc queries) | |||
Generating excel combinations | Excel Discussion (Misc queries) | |||
Benefits of using excel as a proramming solution | Excel Discussion (Misc queries) | |||
What is Multiple R, it is the first solution obtained in Regressi. | Excel Worksheet Functions |