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