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

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" <Jane@discussions.microsoft.com> wrote in message
news:93346910-FD79-45B0-B426-0928883B421A@microsoft.com...
> 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" <Jane@discussions.microsoft.com> wrote in message
news:93346910-FD79-45B0-B426-0928883B421A@microsoft.com...
> 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" <Jane@discussions.microsoft.com> wrote in message
news:93346910-FD79-45B0-B426-0928883B421A@microsoft.com...
> 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" <Jane@discussions.microsoft.com> wrote in message
> news:93346910-FD79-45B0-B426-0928883B421A@microsoft.com...
> > 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" <Jane@discussions.microsoft.com> wrote in message
> news:93346910-FD79-45B0-B426-0928883B421A@microsoft.com...
> > 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" <Jane@discussions.microsoft.com> wrote in message
> news:93346910-FD79-45B0-B426-0928883B421A@microsoft.com...
> > 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
>
>
>