On 2002-02-24 21:46, Bob Matthews wrote:
I have a spreadsheet with lots of daily transactions and a balance after each transaction. There could be more than one transaction per day while some days may have none. I am trying to work out a formula which identifies the first and last transaction for each month so I end up with a monthly opening and closing balance for each month. A sample of the data is below:
Date Type Qty Balance
01-Feb-99 Sales -2 103
01-Feb-99 Sales -1 102
08-Feb-99 Sales -2 100
26-Feb-99 Sales -3 97
26-Feb-99 Sales -6 91
01-Mar-99 Sales -1 90
01-Mar-99 Sales -2 88
31-Mar-99 Sales -4 84
31-Mar-99 Sales -2 82
<snip>
Bob,
What follows might be a bit heavy (if not convoluted).
I'll assume that A1:D12 houses the sample data including labels. By the way, I added 2 more records to the sample:
{"Date","Type","Qty","Balance";
36192,"Sales",-2,103;
36192,"Sales",-1,102;
36199,"Sales",-2,100;
36217,"Sales",-3,97;
36217,"Sales",-6,91;
36220,"Sales",-1,90;
36220,"Sales",-2,88;
36250,"Sales",-4,84;
36250,"Sales",-2,82;
36253,"Sales",-7,78;
36278,"Sales",-8,67}
In F1 enter:
=MATCH(9.99999999999999E+307,A:A)
In F2:H2 enter:
{1999,
"First Transaction",
"Last Transaction"}
In F3:F14 enter:
{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}
Note 1. I've chosen 3-letter month names. A different choice requires adapting the formulas that follow.
In G3 array-enter:
=INDEX(D:D,MATCH(MIN(IF(TEXT(OFFSET($A$2,0,0,$F$1,1),"mmm")=$F3,(OFFSET($A$2,0,0,$F$1,1)))),OFFSET($A$2,0,0,$F$1,1),0)+1)
In H3 array-enter:
=INDEX(D:D,MATCH(MAX(IF(TEXT(OFFSET($A$2,0,0,$F$1,1),"mmm")=$F3,(OFFSET($A$2,0,0,$F$1,1)))),OFFSET($A$2,0,0,$F$1,1),0)+COUNTIF(A:A,MAX(IF(TEXT(OFFSET($A$2,0,0,$F$1,1),"mmm")=$F3,(OFFSET($A$2,0,0,$F$1,1))))))
Note 2. In order to array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.
Select G3:H3 and copy down till the Dec row.
This is what you get to see in F1:H6, showing only Jan to Apr results:
{12,"","";
1999,"First Transaction","Last Transaction";
"Jan",#N/A,#N/A;
"Feb",103,91;
"Mar",90,82;
"Apr",78,67}
Note that we have #N/A for Jan, due to the absence of data regarding that month. If you'd like not to see #N/A's, you can apply Cond Format to the result range to whiten them.
Aladin