# Using existing figures to make a specified total.

#### Mattrixdesign

##### Board Regular
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).

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
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

##### Well-known Member
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
ABCD
2ChoicesUsed?TargetResult
31681150000
42620
52720
62981
73301
84301
95531
106551
118010
1210091
1313590
1414940
1516750
1618190
1718450
1821500
1921760
2023410
2128280
2229920
2330190
2430950
2531370
2634270
2736950
2837801
2938751
3039021
3139580
Sheet2

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

Replies
3
Views
529
Replies
3
Views
1K
Replies
29
Views
9K

1,186,918
Messages
5,960,568
Members
438,486
Latest member
ncc84330

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back