• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

A combination of three tables gives the amount 20

bines53

Active Member
Hello friends,

There are three columns, each column, 10 values, need to find the formula, which enable combinations, one value from column to give the amount to 20.
The rule is possible to use the value in Column A, only once, and in two other columns, there is no limit.
Without volatile functions,
The result should be 7 combinations.
I came to the result, with 108 characters without character {} at the beginning and at the end of the formula.
Some values are highlighted for illustration.

Good luck !


David
 

Attachments

  • test .chall.xlsx
    8.8 KB · Views: 51
Last edited:
Hello friends,

I made a small change in the formula, and I got to 84 characters.

Next Monday, I will present the formula, and another formula to give, how many possible combinations, in any amount, from the formula.

David.
 
Hi John,

Very similar to mine, the solution gives the desired result, a number of characters equal to mine, just do not see any value in column A, include in results 7.

Very nice .

David
 
Hello friends,

This week passed very quickly,

The formula,

=COUNT(IF(MATCH(20-A2:A11,SMALL(B2:B11+TRANSPOSE(C2:C11),ROW(A1:A100)),0),A2:A11,""))

enter, CTRL +SHIFT +ENTER.(CSE)

Put the cursor on the COUNT and pressing the function button, see

{2;14;#N/A;#N/A;10;12;4;6;8;#N/A},
Get seven values in column A gives the combinations, the sum of 20.
If you do not want them values, then it is possible shorter function.

=COUNT(MATCH(20-A2:A11,SMALL(B2:B11+TRANSPOSE(C2:C11),ROW(A1:A100))))
enter, CTRL +SHIFT +ENTER

Now,
We have a table, three columns, each column has 10 lines, which means there are 1000 combinations, 10 ^ 3, we want to know how many combinations there are, if the sum is 9, or any other amount, the formula is

=SUM(--(SMALL($B$2:$B$11+TRANSPOSE($C$2:$C$11),ROW(A1:A100))+TRANSPOSE(A2:A11)=9))

enter, CTRL +SHIFT +ENTER

I hope you enjoyed, and thank you for your cooperation.

Soon I'll make something more challenging.

David
 
Back
Top