0

I have a column in which numbers are written. I need from these numbers to find all combinations of 5 numbers that will give a sum less than 3000 in the enclosed example. The number in column a can only be used once in each combination. Is This possible?

enter image description here

koen
  • 5,383
  • 7
  • 50
  • 89
Tom Farrell
  • 1
  • 1

1 Answers1

0

You may try this, the formula is modified version of all_possible_combinations from this thread here

=let(range,map(A1:A10,lambda(Σ,wraprows(Σ,5,Σ))),data,filter(range,bycol(range,lambda(Σ,counta(Σ)))<>0),
     count,bycol(data,lambda(Σ,counta(Σ))), column,sequence(1,columns(data),1),
     first,tocol(map(tocol(choosecols(data,1),1),lambda(Σ,wraprows(Σ,product(ifna(filter(count,column>1),1)),Σ)))),
     combo_,if(max(column)=1,first,reduce(first,sequence(1,max(column)-1,2,1),lambda(a,c,{a,
           tocol(map(tocol(map(tocol(choosecols(data,c),1),lambda(Σ,wraprows(Σ,product(ifna(filter(count,column>c),1)),Σ)))),lambda(Σ,wraprows(Σ,product(filter(count,column<c)),Σ))),,1)}))),
     Λ,unique(byrow(combo_,lambda(Σ,torow(sort(tocol(Σ),1,1))))),
       filter(Λ,byrow(Λ,lambda(Σ,countunique(Σ)=5)),byrow(Λ,lambda(Σ,sum(Σ)))<B1))

enter image description here

rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19