??SUMPRODUCT? other solution?

[Guest]

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

 

[Bob Phillips]

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)

 

[Bernard Liengme]

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 Liengme]

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?

 

[Guest]

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...

 

[Guest]

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...

 

[Guest]

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