Average of last X transactions by customer

[BloodyBill]

Hi,
I have a growing data set of 24K+ transactions made by more than 3,200 customers. I want to see an average number of units purchased by each customer. Easy. A pivot table with data summarized by average does the trick.

Except, I want to see the average based on recency. In other words, if Customer A has 35 transactions in the data set, what is the average of his last 4 transactions? His last 5? His last 3? I don't want to filter by date, because that might eliminate infrequent customers whose data is spread out over time.

Also, I need one more thing: If I'm averaging all the customers' most recent 5 transactions, but new Customer Q only has 3 or 4 transactions, I need the average of whatever I can get.

The dummy data below is pretty close to what I have: A transaction date. A unique customer identifier. And number of products at each transaction. (BTW, never more than one transaction per day.) The only difference is that I have 24K lines of data (transactions) and 3,200 customer identifiers.

Any help? (Especially non-VBA solutions).

Thanks!!

Date	Customer	Products purchased
5/15/2024	A	2
5/15/2024	B	2
5/14/2024	C	1
4/30/2024	A	3
3/3/1901	B	1
11/17/1900	C	1
11/11/1900	A	3
3/5/2024	B	2
2/26/2024	C	3
2/15/2024	A	2
2/14/2024	B	1
2/2/2024	C	1
1/31/2024	A	3
1/31/2024	B	1
1/27/2024	C	1
1/28/2024	A	3
1/12/2024	B	2
1/2/2024	C	3

 

[awoohaw]

This is a little clunky, i may have some extraneous arguments. But, it seems to work:

Book1
	A	B	C	D	E	F	G
1	Date	Customer	Products purchased			TranDepth	4
2	2024-05-15	A	2			A	2.50
3	2024-05-15	B	2			B	1.50
4	2024-05-14	C	1			C	1.50
5	2024-04-30	A	3
6	1901-03-03	B	1
7	1900-11-17	C	1
8	1900-11-11	A	3
9	2024-03-05	B	2
10	2024-02-26	C	3
11	2024-02-15	A	2
12	2024-02-14	B	1
13	2024-02-02	C	1
14	2024-01-31	A	3
15	2024-01-31	B	1
16	2024-01-27	C	1
17	2024-01-28	A	3
18	2024-01-12	B	2
19	2024-01-02	C	3
20
Sheet3

Cell Formulas
Range	Formula
F2:F4	F2	=SORT(UNIQUE($B$2:$B$19))
G2:G4	G2	=LET(AD,$A$2:$C$19,D,$A$2:$A$19,C,$B$2:$B$19,P,$C$2:$C$19, CustData,SORT(FILTER(AD,C=$F2),1,-1),N,$G$1,Cdates,CHOOSECOLS(CustData,1),NthDate,LARGE(Cdates,N), NthProds,FILTER(AD,((D>=NthDate)*(C=$F2)),""),AVERAGE(CHOOSECOLS(NthProds,3)) )
Dynamic array formulas.

 

[Dave Patton]

N.B. I used Text to Columns to create real dates and I sorted on the Date field.

T202405.xlsm
	A	B	C	D	E	F
1	Date	Customer	Products purchased		Last	4
2	11-Nov-00	A	3		A	2.50
3	17-Nov-00	C	1		C	1.50
4	03-Mar-01	B	1		B	1.50
5	02-Jan-24	C	3
6	12-Jan-24	B	2
7	27-Jan-24	C	1
8	28-Jan-24	A	3
9	31-Jan-24	A	3
10	31-Jan-24	B	1
11	02-Feb-24	C	1
12	14-Feb-24	B	1
13	15-Feb-24	A	2
14	26-Feb-24	C	3
15	05-Mar-24	B	2
16	30-Apr-24	A	3
17	14-May-24	C	1
18	15-May-24	A	2
19	15-May-24	B	2
1g

Cell Formulas
Range	Formula
E2:E4	E2	=UNIQUE(B2:B19)
F2:F4	F2	=AVERAGE(TAKE(FILTER($C$2:$C$19,$B$2:$B$19=E2),-$F$1))
Dynamic array formulas.

 

[awoohaw]

T202405.xlsm
	A	B	C	D	E	F
1	Date	Customer	Products purchased		Last	4
2	15-May-24	A	2		A	2.75
3	15-May-24	B	2		B	1.50
4	14-May-24	C	1		C	2.00
5	30-Apr-24	A	3
6	03-Mar-01	B	1
7	17-Nov-00	C	1
8	11-Nov-00	A	3
9	05-Mar-24	B	2
10	26-Feb-24	C	3
11	15-Feb-24	A	2
12	14-Feb-24	B	1
13	02-Feb-24	C	1
14	31-Jan-24	A	3
15	31-Jan-24	B	1
16	27-Jan-24	C	1
17	28-Jan-24	A	3
18	12-Jan-24	B	2
19	02-Jan-24	C	3
1g

Cell Formulas
Range	Formula
E2:E4	E2	=UNIQUE(B2:B19)
F2:F4	F2	=AVERAGE(TAKE(FILTER($C$2:$C$19,$B$2:$B$19=E2),-$F$1))
Dynamic array formulas.

I knew it could be much more succinct!

 

[BloodyBill]

Thanks! That did the trick!!

 

[Dave Patton]

Thanks for the feedback