Formula Bottleneck- identifying the % of promotional transaction that each user has

[dionlkk6]

Hi!

I've got a data sheet containing some 20,000 rows, regarding a series of consumer transactions. The cell reference of interest is B (which shows a particular user_id, ranging from A, B,C, DD,EE etc..), and S ( which indicates for a particular transaction, if a consumer has used a promotion- with 1 denoting "yes", and "0" denoting no).

Is there anyway I can find the approximate percentage of promotional transaction that each consumer ? I.e for user A, only 1 (cell S2), out of 9 transactions (cells S2-S9) used a promotions i.e about 89% of transaction did not?

Is there a formula that I can write to test this for all 20,000 cells , which contains approximately 5,000 different users?

 

[Toadstool]

Hi dionlkk6,

The tricky part for an Excel 2016 user will be getting that unique list of user_ids for the calculation. The formula in W2 and down will work but when I ran it for 5,000 user_ids I cancelled after 5 minutes as it was only 40% complete. It would be faster to do manually with a copy & paste of B2 down into W2 down and then Data, Remove Duplicates.

The calculation of percentage Used Promotion/Promotion Not Used is fast for even 50,000 rows of Month data.

dionlkk6.xlsx
	A	B																	S	T	U	V	W	X	Y
1	Month	UserId																	is_promotion_to_use	TotalCost			Userid	Used Promotion	Promotion Not Used
2	November	A																	1	3.64			A	42.86%	57.14%
3	November	A																	0	10.93			B	0.00%	100.00%
4	November	A																	0	7.54			C	25.00%	75.00%
5	November	A																	0	7.92
6	November	A																	0	7.29
7	November	A																	1	1.1
8	November	A																	1	7.78
9	November	B																	0	10.66
10	November	B																	0	1.85
11	November	B																	0	6.08
12	November	C																	0	4.35
13	November	C																	0	5.04
14	November	C																	0	10.78
15	November	C																	1	2.59
dionlkk6

Cell Formulas
Range	Formula
W2:W15	W2	=INDEX($B$2:$B$999,MATCH(0,INDEX(COUNTIF($W$1:$W1,$B$2:$B$999),),0))&""
X2:X15	X2	=IF($W2="","",COUNTIFS($B$2:$B$49999,$W2,$S$2:$S$49999,1)/COUNTIFS($B$2:$B$49999,$W2))
Y2:Y15	Y2	=IF($W2="","",COUNTIFS($B$2:$B$49999,$W2,$S$2:$S$49999,0)/COUNTIFS($B$2:$B$49999,$W2))