On 2002-04-20 06:05, met wrote:
my data includes real dates and collection payment of sales agent every week. my summary would look like this...
04/1-7/02 04/08-14/02 04/15-21/02
Agent A 500.00 0 350.00
Agent B 1000.00 500.00 0
Agent C 0 0 1500.00
i hope this would help...thanks
met
m really sorry...i cant align the columns!
This message was edited by met on 2002-04-20 06:06
I'll assume that you have your data in A to C in a sheet called
Data like the sample that follows:
{"Date","Agent","Amount";
37347,"A",3;
37353,"A",3;
37358,"B",2;
37359,"C",5;
37362,"A",4;
37366,"B",1;
37375,"B",6;
37376,"C",2}
and the actual data start at row 2. The above sample is in A1:C9.
In D1 enter: Week
In D2 enter and copy down:
=WEEKNUM(A2)
Note 1. WEEKNUM is available thru Analysis Toolpak (which, if needed, can be activated via Tools|Add-Ins).
I'll assume that the data area grows by new additions. Therefore we are going to define dynamic name ranges which you can also use in any other analysis of the data that you might want to carry out.
Activate Insert|Name|Define.
Enter
NumRecs as name in the Names in Workbook box.
Enter as formula in the Refers to box:
=MATCH(9.99999999999999E+307,Data!$A:$A)
Note 2. The formula refers explicitly to the column that houses dates.
Activate Add. (Don't leave yet the Define Name window.)
Enter
DataRecs as name in the Names in Workbook box.
Enter as formula in the Refers to box:
=NumRecs-(ROW(Data!$A$2)-1)
Activate Add. (Don't leave yet the Define Name window.)
Enter
DATES as name in the Names in Workbook box.
Enter as formula in the Refers to box:
=OFFSET(Data!$A$2,0,0,DataRecs,1)
Activate Add. (Don't leave yet the Define Name window.)
Enter
AGENTS as name in the Names in Workbook box.
Enter as formula in the Refers to box:
=OFFSET(Data!$B$2,0,0,DataRecs,1)
Activate Add. (Don't leave yet the Define Name window.)
Enter
AMOUNTS as name in the Names in Workbook box.
Enter as formula in the Refers to box:
=OFFSET(Data!$C$2,0,0,DataRecs,1)
Activate Add. (Don't leave yet the Define Name window.)
Enter
WEEKS as name in the Names in Workbook box.
Enter as formula in the Refers to box:
=OFFSET(Data!$D$2,0,0,DataRecs,1)
Activate OK.
Note 2. You can also give a name the whole area,
DTable using the foregoing procedure with the formula:
=OFFSET(Data!$A$2,0,0,DataRecs,4).
Go now to a worksheet called
Summary.
Enter in row 2 from B2 on: 04/1-7/02, 04/08-14/02, 04/15-21/02, etc.
Enter in B3:
=WEEKNUM(MIN(DATES))
Enter in C3 and copy across:
=B3+1
In B4 enter and copy across:
=COUNTIF(WEEKS,B3)
This gives you a weekly transaction count.
In B5 enter and copy across:
=SUMIF(WEEKS,B3,AMOUNTS)
This gives you a weekly transaction total.
Enter a unique list of agents from
A6 on downwards (You can do this with Advanced Filter or manually).
In B6 enter:
=SUMPRODUCT((AGENTS=$A6)*(WEEKS=B$3),AMOUNTS)
This gives you weekly totals for each agent.
Copy this first across then down as far as needed.
Another method to get such a summary is by using Pivot Tables.
Aladin