Forum Discussion

Pritu650's avatar
Pritu650
Copper Contributor
Jun 15, 2022

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           ...
BI & Data Analysis
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jun 15, 2022

HansVogelaar 

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.

 

Resources