Selecting names from a list based on an optimised sum

AwEsome

New Member
Joined
Mar 15, 2004
Messages
2
Ok, I hope I can explain this without confusing it. I might as well explain exactly what I am trying to do so it might help.

I have a list of names, say 100. Each name has a value and a score. I need to select from this list of names a sub list, of say 30, where the total value doesn't exceed a certain amount, and the total of the scores for that sub list is the highest possible out of all the possible combinations.

There are some more comlications to this, but I will start with that for the moment. I have thought of a combination of CSEs and using Solver, but I don't seem to be on the right track.

Any ideas?

Thanks

Andrew
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Without knowing your exact requirements, a general solution. Make a new column with a formula (eg. if(A1>100,"High Score","") and sort he list on this, or use Data/Filter.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"where the total value doesn't exceed a certain amount, and the total of the scores for that sub list is the highest possible out of all the possible combinations."

care to elaborate on how these 2 criteria might interact with the help of a few examples...
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Check out if this is what you are after.

Formulas in E column and I2 are array formulas and must be entered with Ctrl + Shift + Enter.

Formulas in E and F most be dragged down 30 rows.
Book3
ABCDEFGHI
1NameValueScoreNameScoreMaxValueMinScore
2a1505a29910065
3a25199a398
4a35298a497
5a45397a1091
6a55423a1190
7a6550a1289
8a7560a1388
9a8570a1487
10a9580a1586
11a105991a1685
Sheet1
 

AwEsome

New Member
Joined
Mar 15, 2004
Messages
2
Thanks fairwinds, but it doesn't quite do it. Obviously I need to explain a bit more.
Starting again.
I have 100 names, each with a (dollar) value (say between $100 and $1000) and a score (say out of 100).
I need a subset of 30 names with the following restrictions:
- The total value of the subset must not exceed a certain amount (say $20,000)
- The total score of the subset needs to be as high as possible.

The thought I am having at the moment is that I need a way of selecting a subset of 30 names and testing whether that subset meets my requirements, maybe throwing Solver in there for good measure.

Thanks

Andrew
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
My formula returned the 30 highest scores that has a value of 100 or less.

I cannot see how it would be possible to achieve what you ask. There must be several answers to your query. What decides what result to show?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

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