Formula for analysing customer cohorts

[rcmander]

I have developed a formula which shows customer activity by cohort (each month a new customer "Cohort" comes on stream). The pattern is linear and in each month each customer who is active buys one item.

As an example CUstomers in their first year will buy three times Month 1,5, and 9. In Month 1 their are 10 purchases, so I know that is 10 customers, in week 5 there are 241 purchases so I know the size of the Week 5 cohort must be 231 (as the only other active cohort is the week 1 Cohort). My formula might change the purchasing pattern so want a formula to work out how many people are in each cohort based on sales activity.

Any help greatly received - I can usually find a solution for most problems but this is defeating me

Cohorts.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
5													Jan 23	Feb 23	Mar 23	Apr 23	May 23	Jun 23	Jul 23	Aug 23	Sep 23	Oct 23
6			Start Date		Needs fleshing out								1 Jan 23	1 Feb 23	1 Mar 23	1 Apr 23	1 May 23	1 Jun 23	1 Jul 23	1 Aug 23	1 Sep 23	1 Oct 23
7			End Date										31 Jan 23	28 Feb 23	31 Mar 23	30 Apr 23	31 May 23	30 Jun 23	31 Jul 23	31 Aug 23	30 Sep 23	31 Oct 23
8			Financial year										31 Dec 23	31 Dec 23	31 Dec 23	31 Dec 23	31 Dec 23	31 Dec 23	31 Dec 23	31 Dec 23	31 Dec 23	31 Dec 23
9			Year end										No	No	No	No	No	No	No	No	No	No
10			Number of Days										31	28	31	30	31	30	31	31	30	31
11			Counter										1	2	3	4	5	6	7	8	9	10
12
13		1.	Business volume / Active users (see Growth curve)
14
15			SALES
16			Total items sold									0	10	11	63	145	241	350	474	612	763	928
17
18			Cohort 1								6	-	1	-	-	-	1	-	-	-	1	-
19			Cohort 2								6	-	-	1	-	-	-	1	-	-	-	1
20			Cohort 3								6	-	-	-	1	-	-	-	1	-	-	-
21			Cohort 4								6	-	-	-	-	1	-	-	-	1	-	-
22			Cohort 5								6	-	-	-	-	-	1	-	-	-	1	-
23			Cohort 6								6	-	-	-	-	-	-	1	-	-	-	1
24			Cohort 7								6	-	-	-	-	-	-	-	1	-	-	-
25			Cohort 8								6	-	-	-	-	-	-	-	-	1	-	-
26			Cohort 9								6	-	-	-	-	-	-	-	-	-	1	-
27			Cohort 10								6	-	-	-	-	-	-	-	-	-	-	1
28			Cohort 11								6	-	-	-	-	-	-	-	-	-	-	-
29			Cohort 12								6	-	-	-	-	-	-	-	-	-	-	-
30			Cohort 13								5	-	-	-	-	-	-	-	-	-	-	-
31			Cohort 14								5	-	-	-	-	-	-	-	-	-	-	-
32			Cohort 15								5	-	-	-	-	-	-	-	-	-	-	-
33			Cohort 16								5	-	-	-	-	-	-	-	-	-	-	-
34			Cohort 17								5	-	-	-	-	-	-	-	-	-	-	-
Income and operations calcs (2)

Cell Formulas
Range	Formula
M16:V16	M16	=M99
K18:K34	K18	=SUM(M18:AV18)