Forum Discussion
How to find multiple co-relations
Hi
I have a data set here , which looks similar to this
| Order Id | apple | Banana | Orange | Kiwi | Total |
| 1 | 2 | 2 | |||
| 2 | |||||
| 3 | 2 | 1 | 3 | ||
| 4 | |||||
| 5 | 1 | 4 | 5 | ||
| 6 | 4 | 4 | |||
| 7 | 3 | 6 | 9 | ||
| 8 | 2 | 2 | 4 | ||
| 9 | 2 | 1 | 1 | 4 | |
| 10 | 4 | 5 | 9 | ||
| Total | 11 | 11 | 11 | 7 | 40 |
The order Ids are unique and the numbers mentioned in the table are number of qty sold.
For example Order no 8 has 2 bananas and 2 oranges.
I need help in figuring out - Often Bought with - How many times apples were bought with oranges ? or how many times or orders have oranges and kiwi both ?
5 Replies
Another way - see the attached workbook.
Same solution again but this time using MAKEARRAY:
= MAKEARRAY(4,4, LAMBDA(r,c, IF(r<>c, COUNTIFS( INDEX(qty,,r), ">0", INDEX(qty,,c), ">0" ), "-" ) ) )Works well. I did wonder about COUNTIFS, maybe within MAKEARRAY, but didn't get to pull it together. To understand the formula, I worked through it naming references as I recognised them (so making the formula comprehensible for me and incomprehensible for everyone else!)
= IF(@item1<>@item2, COUNTIFS( XLOOKUP(@item1,item,qty),">0", XLOOKUP(@item2,item,qty),">0" ), "-")As for timing, the range of relative reference formulas appeared a bit slow first time through but the same as an array formula on subsequent trials.
=SUMPRODUCT((G3:G12<>"")*(H3:H12<>""))=SUMPRODUCT((I3:I12<>"")+(J3:J12<>""))You can try these formulas for apples bought with bananas and for oranges or kiwi both.
I am not sure whether there is a standard way of doing this? The formula returns the number of times each combination occurs.
The formula is a 365 array formula because I have pretty much forgotten traditional methods even when they may be simpler (more often they are not).
