Selecting names from a list based on an optimised sum

[AwEsome]

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

 

[BrianB]

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]

"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]

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
	A	B	C	D	E	F	G	H	I
1	Name	Value	Score		Name	Score		MaxValue	MinScore
2	a1	50	5		a2	99		100	65
3	a2	51	99		a3	98
4	a3	52	98		a4	97
5	a4	53	97		a10	91
6	a5	54	23		a11	90
7	a6	55	0		a12	89
8	a7	56	0		a13	88
9	a8	57	0		a14	87
10	a9	58	0		a15	86
11	a10	59	91		a16	85
Sheet1

 

[AwEsome]

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]

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?