specific sum of combination of numbers in a sheet

  • Hi dears

    first in first i have to apologize for my bad english, im not a native .


    i have sheet with 200 numbers in column A( i.e from 1 to 200)


    now i want to know which combination of two ( or three, or four or five or sex ) numbers equal 75 ?


    i know its very difficault , but is there any way ??!?

  • Re: specific sum of combination of numbers in a sheet


    for the list 1 to 75
    with target of 75 with 3 items some of the results returned are
    1,2,72 75
    1,3,71 75
    1,4,70 75
    1,5,69 75
    1,6,68 75
    1,7,67 75
    1,8,66 75
    1,9,65 75
    1,10,64 75
    1,11,63 75
    1,12,62 75
    1,13,61 75
    1,14,60 75
    1,15,59 75
    1,16,58 75
    1,17,57 75
    1,18,56 75
    1,19,55 75
    1,20,54 75
    1,21,53 75
    1,22,52 75
    1,23,51 75
    1,24,50 75
    1,25,49 75
    1,26,48 75
    1,27,47 75
    1,28,46 75
    1,29,45 75
    1,30,44 75
    1,31,43 75
    1,32,42 75
    1,33,41 75
    1,34,40 75
    1,35,39 75
    1,36,38 75
    2,3,70 75
    2,4,69 75
    2,5,68 75
    2,6,67 75
    2,7,66 75
    2,8,65 75
    2,9,64 75
    2,10,63 75
    2,11,62 75
    2,12,61 75
    2,13,60 75
    2,14,59 75
    2,15,58 75
    2,16,57 75
    2,17,56 75
    2,18,55 75
    2,19,54 75
    2,20,53 75
    2,21,52 75
    2,22,51 75
    2,23,50 75
    2,24,49 75
    2,25,48 75
    2,26,47 75
    2,27,46 75
    2,28,45 75
    2,29,44 75
    2,30,43 75
    2,31,42 75
    2,32,41 75
    2,33,40 75
    2,34,39 75
    2,35,38 75
    2,36,37 75
    3,4,68 75
    3,5,67 75
    3,6,66 75
    3,7,65 75
    3,8,64 75
    3,9,63 75
    3,10,62 75
    3,11,61 75
    3,12,60 75
    3,13,59 75
    3,14,58 75
    3,15,57 75
    3,16,56 75
    3,17,55 75
    3,18,54 75
    3,19,53 75
    3,20,52 75
    3,21,51 75
    3,22,50 75
    3,23,49 75
    3,24,48 75
    3,25,47 75
    3,26,46 75
    3,27,45 75
    3,28,44 75
    3,29,43 75
    3,30,42 75
    3,31,41 75
    3,32,40 75
    3,33,39 75
    3,34,38 75
    3,35,37 75
    4,5,66 75
    4,6,65 75
    4,7,64 75
    4,8,63 75
    4,9,62 75
    4,10,61 75
    4,11,60 75
    4,12,59 75
    4,13,58 75
    4,14,57 75
    4,15,56 75
    4,16,55 75
    4,17,54 75
    4,18,53 75
    4,19,52 75
    4,20,51 75
    4,21,50 75
    4,22,49 75
    4,23,48 75
    4,24,47 75
    4,25,46 75
    4,26,45 75
    4,27,44 75
    4,28,43 75
    4,29,42 75
    4,30,41 75
    4,31,40 75
    4,32,39 75
    4,33,38 75
    4,34,37 75
    4,35,36 75
    5,6,64 75
    5,7,63 75
    5,8,62 75
    5,9,61 75
    5,10,60 75
    5,11,59 75
    5,12,58 75
    5,13,57 75
    5,14,56 75
    5,15,55 75
    5,16,54 75
    5,17,53 75
    5,18,52 75
    5,19,51 75
    5,20,50 75
    5,21,49 75
    5,22,48 75
    5,23,47 75
    5,24,46 75
    5,25,45 75
    5,26,44 75
    5,27,43 75
    5,28,42 75
    5,29,41 75
    5,30,40 75
    5,31,39 75
    5,32,38 75
    5,33,37 75
    5,34,36 75
    6,7,62 75
    6,8,61 75
    6,9,60 75
    6,10,59 75
    6,11,58 75
    6,12,57 75
    6,13,56 75
    6,14,55 75
    6,15,54 75
    6,16,53 75
    6,17,52 75
    6,18,51 75
    6,19,50 75
    6,20,49 75
    6,21,48 75
    6,22,47 75
    6,23,46 75
    6,24,45 75
    6,25,44 75
    6,26,43 75
    6,27,42 75
    6,28,41 75
    6,29,40 75
    6,30,39 75
    6,31,38 75
    6,32,37 75
    6,33,36 75
    6,34,35 75
    7,8,60 75
    7,9,59 75
    7,10,58 75
    7,11,57 75
    7,12,56 75
    7,13,55 75
    7,14,54 75
    7,15,53 75
    7,16,52 75
    7,17,51 75
    7,18,50 75
    7,19,49 75
    7,20,48 75
    7,21,47 75
    7,22,46 75
    7,23,45 75
    7,24,44 75
    7,25,43 75
    7,26,42 75
    7,27,41 75
    7,28,40 75
    7,29,39 75
    7,30,38 75
    7,31,37 75
    7,32,36 75
    7,33,35 75
    8,9,58 75
    8,10,57 75
    8,11,56 75
    8,12,55 75
    8,13,54 75
    8,14,53 75
    8,15,52 75
    8,16,51 75
    8,17,50 75
    8,18,49 75
    8,19,48 75
    8,20,47 75
    8,21,46 75
    8,22,45 75
    8,23,44 75
    8,24,43 75
    8,25,42 75
    8,26,41 75
    8,27,40 75
    8,28,39 75
    8,29,38 75
    8,30,37 75
    8,31,36 75
    8,32,35 75
    8,33,34 75
    9,10,56 75
    9,11,55 75
    9,12,54 75
    9,13,53 75
    9,14,52 75
    9,15,51 75
    9,16,50 75
    9,17,49 75
    9,18,48 75
    9,19,47 75
    9,20,46 75
    9,21,45 75
    9,22,44 75
    9,23,43 75
    9,24,42 75
    9,25,41 75
    9,26,40 75
    9,27,39 75
    9,28,38 75
    9,29,37 75
    9,30,36 75
    9,31,35 75
    9,32,34 75
    10,11,54 75
    10,12,53 75
    10,13,52 75
    10,14,51 75
    10,15,50 75
    10,16,49 75
    10,17,48 75
    10,18,47 75
    10,19,46 75
    10,20,45 75
    10,21,44 75
    10,22,43 75
    10,23,42 75
    10,24,41 75
    10,25,40 75
    10,26,39 75
    10,27,38 75
    10,28,37 75
    10,29,36 75
    10,30,35 75
    10,31,34 75
    10,32,33 75
    11,12,52 75
    11,13,51 75
    11,14,50 75
    11,15,49 75
    11,16,48 75
    11,17,47 75
    11,18,46 75
    11,19,45 75
    11,20,44 75
    11,21,43 75
    11,22,42 75
    11,23,41 75
    11,24,40 75
    11,25,39 75
    11,26,38 75
    11,27,37 75
    11,28,36 75
    11,29,35 75
    11,30,34 75
    11,31,33 75
    12,13,50 75
    12,14,49 75
    12,15,48 75
    12,16,47 75
    12,17,46 75
    12,18,45 75
    12,19,44 75
    12,20,43 75
    12,21,42 75
    12,22,41 75
    12,23,40 75
    12,24,39 75
    12,25,38 75
    12,26,37 75
    12,27,36 75
    12,28,35 75
    12,29,34 75
    12,30,33 75
    12,31,32 75
    13,14,48 75
    13,15,47 75
    13,16,46 75
    13,17,45 75
    13,18,44 75
    13,19,43 75
    13,20,42 75
    13,21,41 75
    13,22,40 75
    13,23,39 75
    13,24,38 75
    13,25,37 75
    13,26,36 75
    13,27,35 75
    13,28,34 75
    13,29,33 75
    13,30,32 75
    14,15,46 75
    14,16,45 75
    14,17,44 75
    14,18,43 75
    14,19,42 75
    14,20,41 75
    14,21,40 75
    14,22,39 75
    14,23,38 75
    14,24,37 75
    14,25,36 75
    14,26,35 75
    14,27,34 75
    14,28,33 75
    14,29,32 75
    14,30,31 75
    15,16,44 75
    15,17,43 75
    15,18,42 75
    15,19,41 75
    15,20,40 75
    15,21,39 75
    15,22,38 75
    15,23,37 75
    15,24,36 75
    15,25,35 75
    15,26,34 75
    15,27,33 75
    15,28,32 75
    15,29,31 75
    16,17,42 75
    16,18,41 75
    16,19,40 75
    16,20,39 75
    16,21,38 75
    16,22,37 75
    16,23,36 75
    16,24,35 75
    16,25,34 75
    16,26,33 75
    16,27,32 75
    16,28,31 75
    16,29,30 75
    17,18,40 75
    17,19,39 75
    17,20,38 75
    17,21,37 75
    17,22,36 75
    17,23,35 75
    17,24,34 75
    17,25,33 75
    17,26,32 75
    17,27,31 75
    17,28,30 75
    18,19,38 75
    18,20,37 75
    18,21,36 75
    18,22,35 75
    18,23,34 75
    18,24,33 75
    18,25,32 75
    18,26,31 75
    18,27,30 75
    18,28,29 75
    19,20,36 75
    19,21,35 75
    19,22,34 75
    19,23,33 75
    19,24,32 75
    19,25,31 75
    19,26,30 75
    19,27,29 75
    20,21,34 75
    20,22,33 75
    20,23,32 75
    20,24,31 75
    20,25,30 75
    20,26,29 75
    20,27,28 75
    21,22,32 75
    21,23,31 75
    21,24,30 75
    21,25,29 75
    21,26,28 75
    22,23,30 75
    22,24,29 75
    22,25,28 75
    22,26,27 75
    23,24,28 75
    23,25,27 75
    24,25,26 75

  • Re: specific sum of combination of numbers in a sheet


    Quote from pike;787508

    Cool
    as you can see the number of combinations can easily exceed excels capabilities


    dear pike


    there is a problem


    im trying to find combonation of amounts more than 100 000 000 but it cant find them :(

  • Re: specific sum of combination of numbers in a sheet


    depends on the data set it first has to look at all the combinations and that's restrictive the possible millions and millions of loops to process just to find the first few answers


    looking for three combinations to equal 75 with 100 numbers is 10 to the power 100!

  • Re: specific sum of combination of numbers in a sheet


    parameteres = 75 , maximum combination = 5, target value bigger than 100 000 000 and 500 000 000,


    is it posible that i devide my numbers to 1000 000? ( for example) , it will make numbers very small

  • Re: specific sum of combination of numbers in a sheet


    wow .. that still a huge number to crunch for which 5 combinations of 1millon numbers add up to 75 ...
    If you attempt to initialize an array of that size it will exceed the amount of available RAM and evoke a runtime OutOfMemoryException exception error

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!