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

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.
--
Cheers,
Cheryl

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

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
---
"Cheryl Elizabeth" wrote:
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.
--
Cheers,
Cheryl

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

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









Cheryl Elizabeth wrote:
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.

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

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

to see the greatest number of combinations adding up to 120 without
any number being used more than once.

Your data is therefo
{18,20,21,22,26,29,30,31,34,36,38,39,49,51,57,58,6 3,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 wrote:
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.

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

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!
--
Cheers,
Cheryl


"Dana DeLouis" wrote:

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









Cheryl Elizabeth wrote:
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.

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

Thank you Max,

The Tom Ogilvy classic worked for me!!
--
Cheers,
Cheryl


"Max" wrote:

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
---
"Cheryl Elizabeth" wrote:
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.
--
Cheers,
Cheryl

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

Welcome, glad to hear
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Cheryl Elizabeth" wrote in
message ...
Thank you Max,

The Tom Ogilvy classic worked for me!!
--
Cheers,
Cheryl