Statistical
Count exact items in baskets and calculate an attach rate
Use delimiter-aware helper flags to count baskets containing both items and calculate P(B | A), without matching a product name inside a longer name.
Cross-sell co-occurrence and Confidence P(Item B | Item A)
Store one order per row. In F2, flag whether the basket contains the complete anchor item from D2; in G2, flag the cross-sell item from E2. Fill both helpers through row 6.
The helpers lowercase names, normalize comma-space separators, and search for comma-delimited tokens. Laptop therefore does not match Laptopstand. FIND treats wildcard characters literally.
Use canonical item names with no embedded commas and separators "," or ", ". Normalize other whitespace before applying the example. These are basket counts, not unique-customer counts or quantities.
The attach rate is the share of anchor-item baskets also containing the second item. It is not lift: lift would additionally divide by the second item’s overall prevalence.
| OrderID (A) | Basket Items (B) | Has "Laptop"? | Has "Laptop" + "Dock"? |
|---|---|---|---|
| ORD-1 | Laptop, Dock, Mouse | Yes | Yes (#1) |
| ORD-2 | Monitor, Cable | No | No |
| ORD-3 | Dock, Laptop | Yes | Yes (#2 — order-independent!) |
| ORD-4 | Laptop, Sleeve | Yes | No |
| ORD-5 | Keyboard, Laptop, Dock | Yes (4 total) | Yes (#3 -> 75% Attach!) |
Paired co-occurrence count and Cross-Sell Attach Rate formulas
F and G contain TRUE/FALSE item flags. SUMPRODUCT counts rows where both are true.
=SUMPRODUCT(F2:F6*G2:G6)Cross-Sell Attach Rate P(Item E2 | Item D2)
Divide paired baskets by the number of anchor-item baskets. When the anchor has no matching baskets, return an explicit message instead of dividing by zero.
=IF(COUNTIF(F2:F6,TRUE)=0,"No anchor baskets",ROUND(SUMPRODUCT(F2:F6*G2:G6)/COUNTIF(F2:F6,TRUE),4))Create the anchor-item helper
Enter this in F2 and fill down through F6. For G2:G6, use E2 instead of D2.
=AND($D$2<>"",ISNUMBER(FIND(","&LOWER($D$2)&",",","&LOWER(SUBSTITUTE(B2,", ",","))&",")))Try the example
Use the original values or change them to test your understanding.