Find numbers in a range that sum up to a specified number

[Cheryl Elizabeth]

Hi there,

I regularly need to search through a lists of numbers to find a combination
that will equal a specified number ... is there a function that can help me?

For example, in the following list of numbers, I need to find the numbers,
or cells that equal 120.

31
18
18
26
26
18
26
34
34


21
69


30

98
36
51
22
29
49
63
38
39
20
22
22
20
22
57
58

One solution is: (this may not be the first solution a function would
return, just the first one I found through trial and error)
26
26
26
22
20

If the function can not find a combination equal to my specified number of
120, then, I would like the function to return the combination of numbers
that is as close to 120 without going over.

In fact, and now I'm probably asking for the impossible, I would like to see
the greatest number of combinations adding up to 120 without any number being
used more than once.

Anyone know how this can be accomplished in Excel? I would hugely
appreciate your help and suggestions.

 

[Max]

Try this Tom Ogilvy classic for a way using Solver:
http://tinyurl.com/5kx9bw

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Dana DeLouis]

If the function can not find a combination equal to ...120

Hi. I see you have duplicate data in your set.


Size 2

{22, 98}, {51, 69}, {57, 63}

Size 3

{18, 39, 63}, {20, 31, 69}, {20, 49, 51}, {21, 30, 69}, {21, 36,
63}, {22, 29, 69}, {26, 31, 63}, {26, 36, 58}, {29, 34, 57},
{30, 39, 51}, {31, 38, 51}

Size 4

{18, 18, 21, 63}, {18, 18, 26, 58}, {18, 20, 31, 51}, {18, 21, 30,
51}, {18, 22, 22, 58}, {18, 22, 29, 51}, {18, 22, 31, 49}, {18, 29,
34, 39}, {18, 30, 34, 38}, {20, 20, 22, 58}, {20, 20, 29, 51}, {20,
20, 31, 49}, {20, 21, 22, 57}, {20, 21, 30, 49}, {20, 22, 29,
49}, {20, 26, 36, 38}, {20, 30, 31, 39}, {20, 30, 34, 36}, {21, 22,
26, 51}, {21, 22, 38, 39}, {21, 26, 34, 39}, {21, 29, 31, 39}, {21,
29, 34, 36}, {21, 30, 31, 38}, {21, 31, 34, 34}, {22, 26, 34,
38}, {22, 29, 30, 39}, {22, 29, 31, 38}, {22, 30, 34, 34}, {26, 26,
29, 39}, {26, 26, 30, 38}, {26, 26, 34, 34}, {26, 29, 31, 34}

Size 5

{18, 18, 18, 30, 36}, {18, 18, 20, 26, 38}, {18, 18, 20, 30,
34}, {18, 18, 21, 29, 34}, {18, 18, 22, 26, 36}, {18, 20, 20, 26,
36}, {18, 20, 21, 22, 39}, {18, 20, 21, 30, 31}, {18, 20, 22, 22,
38}, {18, 20, 22, 26, 34}, {18, 20, 22, 29, 31}, {18, 20, 26, 26,
30}, {18, 21, 22, 29, 30}, {18, 21, 26, 26, 29}, {18, 22, 22, 22,
36}, {20, 20, 21, 29, 30}, {20, 20, 22, 22, 36}, {20, 21, 22, 26,
31}, {20, 22, 22, 22, 34}, {20, 22, 22, 26, 30}, {20, 22, 26, 26,
26}, {21, 22, 22, 26, 29}

Size 6

{18, 18, 18, 20, 20, 26}, {18, 18, 18, 22, 22, 22}, {18, 18, 20, 20,
22, 22}



Might be easier to sort your data for display first...

{18, 18, 18, 20, 20, 21, 22, 22, 22, 22, 26, 26, 26, 29, 30, 31, 34,
34, 36, 38, 39, 49, 51, 57, 58, 63, 69, 98}
= = = = =

HTH :>)
Dana DeLouis

 

[Dana DeLouis]

I didn't see the second request of "no numbers being used more than once"

Your data is therefore:
{18,20,21,22,26,29,30,31,34,36,38,39,49,51,57,58,63,69,98}


{22,98}
{51,69}
{57,63}
{18,39,63}
{20,31,69}
{20,49,51}
{21,30,69}
{21,36,63}
{22,29,69}
{26,31,63}
{26,36,58}
{29,34,57}
{30,39,51}
{31,38,51}
{18,20,31,51}
{18,21,30,51}
{18,22,29,51}
{18,22,31,49}
{18,29,34,39}
{18,30,34,38}
{20,21,22,57}
{20,21,30,49}
{20,22,29,49}
{20,26,36,38}
{20,30,31,39}
{20,30,34,36}
{21,22,26,51}
{21,22,38,39}
{21,26,34,39}
{21,29,31,39}
{21,29,34,36}
{21,30,31,38}
{22,26,34,38}
{22,29,30,39}
{22,29,31,38}
{26,29,31,34}
{18,20,21,22,39}
{18,20,21,30,31}
{18,20,22,26,34}
{18,20,22,29,31}
{18,21,22,29,30}
{20,21,22,26,31}


<snip>
HTH
Dana DeLouis

 

[Cheryl Elizabeth]

Thank you Dana,

The solver solution worked for me. After I find a combination of numbers
that equal 120, I then may not use those specific numbers again. I move them
off the list and then rerun the solver on the remaining numbers, some of
which may be duplicates of the ones used in the first solution.

I appreciate your responses ... THANK YOU!

 

[Cheryl Elizabeth]

Thank you Max,

The Tom Ogilvy classic worked for me!!

 

[Max]

Welcome, glad to hear
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik