Forum Discussion

JoeAkpan28's avatar
JoeAkpan28
Copper Contributor
Apr 18, 2024

Difficulty in formulas

Good day, nice to have a nice community like this.  I'm hooked on a task that i gave myself on market basket analysis, i have tried over 6 different combination of functions but i have been getting ...
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Apr 18, 2024

JoeAkpan28 

=MAP(P9:P17,Q9:Q17,LAMBDA(fruit1,fruit2,SUM(BYROW(A2:K9,LAMBDA(r,(COUNTIFS(r,fruit1)>0)*(COUNTIFS(r,fruit2)>0))))))

 

With Office 365 or Excel for the web you can apply this formula which spills the result.

  • JoeAkpan28's avatar
    JoeAkpan28
    Copper Contributor
    Apr 18, 2024

    OliverScheurich  I really appreciate your efforts. Also, The formula doesn't work and then it highlights this particular r as if something is wrong with it

     

    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Apr 18, 2024

      JoeAkpan28 

      =MAP(R6:R13,S6:S13,LAMBDA(fruit1,fruit2,SUM(BYROW(A2:K14000,LAMBDA(r,(COUNTIFS(r,fruit1)>0)*(COUNTIFS(r,fruit2)>0))))))

       

      =MAP(R6:R13,S6:S13,LAMBDA(fruit1,fruit2,SUM(BYROW(A2:K14000,LAMBDA(x,(COUNTIFS(x,fruit1)>0)*(COUNTIFS(x,fruit2)>0))))))

       

      In the attached file i've made an example according to the size of your database. From your screenshot i can't tell why the formula shouldn't work. You can try replacing r by x. Both formulas return the same result in my sample file.

      • JoeAkpan28's avatar
        JoeAkpan28
        Copper Contributor
        Apr 19, 2024

        Now i understand better. My dataset has 14964 rows and 11 columns

         

Resources