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

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

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

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

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

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

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