Optimizing solution for combination of options

[evylmatt]

Hello,
I'm trying to figure out a formula or macro, or whatever, to figure out the best possible combo of things based on price and projected value. There are some restrictions:

- Total price cannot exceed $60,000
- I have 9 items to get into the combination. Of those, there must be 2 from Group 1, 2 from Group 2, 2 from Group 3, 2 from Group 4, and 1 from Group 5.
- Each item included should have a price (currency) and projected value (number, not currency) that I have already established.
- Some of the items may not contain a price or projected value on some days, so these should be excluded.

I have all the data in 3 worksheets (one contains Group 1 and 2, one contains Group 3 and 4, and one contains Group 5). They are clearly marked with their group on each spreadsheet in the workbook. If necessary, they could be split up into 5 sheets. But if they have to be combined into one group, I would need instructions on that too.

I am working in Google docs. I would prefer to stay there, but I can export the values to an Excel workbook and work from there.

Thanks!

[Brendan_Floyde]

This is actually quite a complex problem and will probably require some code / solver to take multiple guesses at combinations. Are you trying to maximise projected value? How long is each list? I'd suggest getting the 5 lists in to one work book, perhaps cut the lists down a bit and overtype with some dummy data that is similiar but doesn't give anything away and post the workbook here. The main thing is to give the column heading so that is anyone does write anything for you you can simply copy the data in.

[evylmatt]

Yes. Trying to maximize projected value. I have attached dummy data. Two tabs. One with the data and one with a target table.

Target table has to have 2 PG, 2 SG, 2 SF, 2 PF and 1 C (9 spots). Salary is max $60,000. Want to maximize Projected Points while staying within salary constraint and using the exact combination listed for the 9 spots. Good luck!

[Brendan_Floyde]

http://office.microsoft.com/en-gb/ex...001124603.aspx

Read this

I've made one change to the methodology as solver only allows 200 input sells so I've had to calculate a points per £ and knock the bottom few off in each group.

Fantasy football excel.xlsx

[evylmatt]

Thanks! I think I can follow and tweak as needed.

[Brendan_Floyde]

Can I point out rule 9 of the forum.............and if I've helped hit the reputation button.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.

[evylmatt]

Great response. Sorry I didn't know the rules. Solved.