Using existing figures to make a specified total.

[Mattrixdesign]

Hello

I need help with this problem. I have 30 "lengths/meters" in colum A:

168
262
272
298
330
430
553
655
801
1009
1359
1494
1675
1819
1845
2150
2176
2341
2828
2992
3019
3095
3137
3427
3695
3780
3875
3902
3958
4680

I need a formula that will look at all these lengths, and try to equal 15,000mtrs, no less, but it can be more (but using the best possible use of the lengths).

Please help, thanks.

 

[onlyadrafter]

Hello,

Found this on a similar thread

ee
http://myweb.cableone.net/twodays/

and download the free file: Find a Numeric Value.zip

What the file demonstrates (from the above web page):
“From within a range of numbers, identify two or more numbers that, when added together, equal or most closely match, the value you are seeking”.

You may be able to adapt the procedures to suit your needs.

No idea if it works or is any use but it may be a start.

 

[Mattrixdesign]

hmmm, thanks for the link! It is what I need but it doesn't seem to work too well - its crashes XL each time.

the guy who wanted the info has done it manualy. thanks any way

 

[onlyadrafter]

Hello,

I know it would have taken ages to calculate, but I gave up (other things to do you know). But if you trim it down to about 10 values and lower the target, it works a dream, honestly.

 

[Seti]

I got Solver to generate a solution in about a minute. The picture below shows the set up. The last row contained 4680 and was not part of the solution. As stated in another thread, this may not be a unique solution. I used the following assumptions for solver:

Set target cell D3 equal to a value of 0,
By changing cells B3:B32,
Subject to the constraints of:
1. B3:B32<=1
2. B3:B32 >=0
3. B3:B32 int

Book1
	A	B	C	D
2	Choices	Used?	Target	Result
3	168	1	15000	0
4	262	0
5	272	0
6	298	1
7	330	1
8	430	1
9	553	1
10	655	1
11	801	0
12	1009	1
13	1359	0
14	1494	0
15	1675	0
16	1819	0
17	1845	0
18	2150	0
19	2176	0
20	2341	0
21	2828	0
22	2992	0
23	3019	0
24	3095	0
25	3137	0
26	3427	0
27	3695	0
28	3780	1
29	3875	1
30	3902	1
31	3958	0
Sheet2

edit: you may need to load the Solver add-in.