Select and combine 5 different items together maximizing 5 different options

[holyexcel]

Hello to all interested

I have a problem to solve, and i have no clue how to do it. Tried with solver plug-in but i think it can't do the things how i want to do.

So, the thing is: We need an equipment of 5 different items, we have a list of available items, we need to select 1 of each, and all 5 combined to fulfill criteria (miss <= 12.5, avoid <= 40, critical <= 40, ) and maximize avoid, critical, max dmg, dmg incr in that order.

eq-esim.xlsx

[Beamernsw]

Hi, just a couple of quick questions to get it straight in my head.
1. your post says "miss <=12.5" but your file says "miss >=12.5" ..... which one?
2. with the stat "max dmg <=50" for example ...... you want numbers as high as possible up to 50 but not over?
3. you want this file to automatically select the best combination of items to match your criteria in their appropriate order of importance?

I have added a heap of helper columns and created drop down boxes to choose your gear from and after selecting 5 pieces it will show the results.
This is a long way from getting the program to do it automatically, but it is a start.
Actually, the more I look at it, the more I'm realising this is way beyond my capabilities.
But it is an interesting challenge.

BTW, this file at the moment only looks at the first 1000 items.

[holyexcel]

Hi, thanks for helping me
On the questions:
1. yes.. the correct is miss >= 12.5, but i don't need it to be max
2. yes, high as possible... the max dmg limit that i put is also wrong so no limit there
3. yes, auto select the best of all items, in .. importance (most important to least important > avoid , crit , max dmg, dmg incr) , maybe they have to be weighted by importance, something like 0.4,0.3,0.2,0.1

[Alf]

As you seems to have got a solution to your problem I'm not sure you need a solver model but I've set up one using a binary grid (range K2:O14) to find the maximum value for all 5 components.

Alf

[holyexcel]

Thank you Alf!!! That is a very decent solution. But it work good when 1 item have 1 option. I added SUM of SUMPRODUCT for all 5 options and i think for now it do the job. I will test this scenario with more items and report it here. Thank you again

no (weighted ) importance of options for now, and it don't select 1 from each items If someone have an idea for that, ill be more than happy

yup, i realized im doing it wrong that way :D

[Alf]

Not sure at the moment what you wish to achieve. Since you "locked" the C2:C14 values in the sumproduct formula i.e. $C$2:$C$14 the first value is the product of the "miss values" times the "binary miss value" and that is correct. But then in cell S2 you multiply the "miss value" with the "binary avoid value" ???? and the formula in cells T, U and V the "miss values" are multiplied with "binary critical", "binary dmg incr" and "binary max dmg"

If you would like to increase the number of items just change the value of the range K17:O17 to 2 or 3 or whatever you like as long as there are sufficient numbers in the range C2:G14
So you can't set say M17 to 2 because you only got 1 value in the F column with this binary set up.

Another option is to set range K2:O14 to "Integer" instead of "binary". This allows Solver to select more than one from each item if you change the range K17:O17 to higher values than 1.

Alf

[Beamernsw]

Hi holyexcel, have you had any further luck with this?
If not then I have an idea, really don't think it would pan out but willing to try, but I would need quite a bit more data to play with.