Using existing figures to make a specified total.

Mattrixdesign

Board Regular
Joined
Mar 6, 2002
Messages
201
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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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
Joined
Mar 6, 2002
Messages
201
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

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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
Joined
May 19, 2002
Messages
2,916
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,102
Messages
5,768,102
Members
425,454
Latest member
khoro

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?

Disable AdBlock

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
Top