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

C

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.
 
D

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
 
D

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
 
C

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Modifying a complex date formual to count months 4
How do I determine which data in a range add up to a fixed number? 5
I can't reference a cell as the criteria in the AVERAGEIFS functio 2
Match 3 Criteria and Return Lowest Numeric Value 16
Count Duplicate Numbers 4
Return a name in the same row 3
calculating the "mean" for a group of numbers 2
Return Row Number of PREVIOUS Numeric Consecutive Duplicate in Column 3

Top