 # How to find multiple co-relations

Occasional Visitor

# 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

# Re: How to find multiple co-relations

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). # Re: How to find multiple co-relations

``=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. # Re: How to find multiple co-relations

Another way - see the attached workbook.

# Re: How to find multiple co-relations

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.

# Re: How to find multiple co-relations

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"
),
"-"
)
)
)``````