How to find multiple co-relations

Occasional Visitor

Hi 

 

I have a data set here , which looks similar to this 

 

Order Id appleBananaOrange KiwiTotal 
12   2
2     
321  3
4     
51 4 5
64   4
7 3 69
8 22 4
921 14
10 45 9
Total111111740

 

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

@Pritu650 

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).

 

image.png

@Pritu650 

=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.

fruit.JPG 

@Pritu650 

Another way - see the attached workbook.

@Hans Vogelaar 

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.

 

@Hans Vogelaar 

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