CODEWITHSUNDEEP
excelvbaexcel-formulaexcel-2010
Utpal Kant
Utpal Kant

Reputation: 9

Find Confidence via applying excel formula

I'm trying to find out the affinity among two products based on 16 transactions happened. First I calculated the support, which means no. of times the product bought in all 16 transactions. Next one is confidence, which is nothing but the occurrence of another product provided the first one has already occurred. Ex:- Confidence (11 when 7 occurred)=P(11intersection 7)= no. of (11 intersection 7)/no.of occurance of 7= (1/16)/(1/16)=100% Similarly, for confidence (11 when 3 occurred)=(1/16)/(4/16)=25%

I want to find the confidence of all the products vs other products by applying excel formula. I'm not able to apply the formula to find the confidence among the products. The sheet is attached here. Confidence (expected Output) is the table where I want to find the formula to find confidence automatically.

Sample Data

enter image description here

enter image description here

Upvotes: 0

Views: 37

Answers (1)

Oliver Leung
Oliver Leung

Reputation: 812

P(X) and P(X|Y) could be done by countif() and countifs(). To identify the correct column, use offset() and match()

with the location of data as this

the formula in cell J3 of the Confidence matrix is:

=COUNTIFS(OFFSET($A$3:$A$18,0,MATCH($I3,$B$2:$G$2,0)),"=TRUE",OFFSET($A$3:$A$18,0,MATCH(J$2,$B$2:$G$2,0)),"=TRUE")/COUNTIF(OFFSET($A$3:$A$18,0,MATCH(J$2,$B$2:$G$2,0)),TRUE)

which you can use to fill the rest of the matrix.

Upvotes: 1

Related Questions