simulation help needed

[maxpower2]

hi. ive been getting some great help from the forum on this spreadsheet. if anyone has a solution or ideas to help with the last part i would be very grateful.

the aim is to pick 6 players from 24 and to maximize the total score the 6 get.

sheet 2
column A sim number.
B1:AQ the player names and below them is the score they received in the sim.
BC:BP is the 6 best scoring players from that rows simulation
BU:CB list of the top 6 players who appeared most often. and if they are under budget
CE2 the budget we have to get all 6 players
CH2:CH29 players cost

this is the problem we have.
currently we are getting the top 6 players for each sim whether they are under or over budget. basically a record of the 6 highest scoring players that sim. but this isnt what we are after.
what we need is a valid result for every sim. ie, 6 players who come in under budget and who scored higher than all other combinations of 6 players under budget.

any help on method or pointing in the right direction very appreciated. my excel level is lower than the sheet as ive had help. can this problem be solved with this method? anything that is unclear give me a shout and i will try to explain again or in more detail

thanks in advance

[Alf]

Perhaps the excel add-in solver could be of help? As I'm on my cellphone for the next 12 days I can't download your file and test a solver model but I'll try to describe a possible setup.

Column A players name, column B players score, column C player costs, a D column used by solver, an E column with formula "=score * D column value"

Assuming players name goes from A1:A24, score B1:B24, player costs C1:C24 then in B26 put a formuls like this "=Sumproduct (B1:B24,D1:D24)"

In D25 you need a formula "=Sum (D1:D24)"

Start solver and define target cell as B26 and set it to max, select cells to change as range D1:D24

Now add the constraints define range D1:D24 as binaries, set D25 = 6 and set range E1:E24 <= value of max pay. I.e. E1=C1*D1, E2=C2*D2 and so forth.

Click button "Solve" and that should be all.

Alf

[maxpower2]

hey alf. thanks for the response. i actually have a solver setup on a different sheet i could put the data in and get the best 6 for a sim. this would only solve one row (or sim) on my sheet but leave maybe 20,000 rows still to do. to many to do manually with a solver. i dont understand how to scale it up though. is a solver a method that could deal with 20,000 simulations and give 20000 results? if its possible that would be great. im still very new to them.

[MrShorty]

If Solver can solve one simulation, then one can set up a loop in VBA that uses Solver to solve any number of simulations (at least, to the limits of the user's patience waiting for the computer to process each simulation). There are several examples on this forum and across the internet of using Solver in VBA to run these loops.
https://peltiertech.com/Excel/SolverVBA.html
https://www.excelforum.com/excel-pro...oop-basic.html

[maxpower2]

will check it out. sounds promising. thanks

[Alf]

The excel solver is by design limited to about 200 lines of constraints but as this kind of problem is a linear one you could try the freebie "OpenSolver" a linear solver developed by the university of Aukland NY. According to the information on the homepage there are no limits to the number lf constraints and it intergrates nicely with excel.

So build your model using the excel solver as "OpenSolver" can read this and get your dolution running "Open Solver"

Google for "OpenSolver" to find the site and download the "OpenSolver" file.

Will try to help you if you have problems but with no access to a PC and only a smartphone my input will be rather limited for the next 12 days.

Alf

[davsth]

Although there are 376740 theoretical combinations for each line so it could take some time to go through 20000 solutions! It will be interesting to hear of your final solution and how long it took!

[maxpower2]

not feasable davsth? i could wait an hr if it worked. would montycarlo be the method im after? i only have a limited understanding of it but id did sound like what i needed. i wouldnt know how to get started with that though.

[davsth]

Its hard to say and I have never used open solver that may be quicker

[maxpower2]

ok. i have a simple solver set up on sheet 4. i have downloaded open solver like alf suggested.
----------
the solver currently works to maximize points on F:38.
the player list is D5:D32. each player costs a different amount E5:E32.
the maximum that can be spent on players is C:37 currently 50,000
F5:F32 are the cells solver is set to change using only binary
P38:AQ38 are the player scores for that sim
players to pick must =6
-----------
P34:AQ34 are just = to F5:F32 these may not be necessary. i added them as my data was set up below.
-----------------------------------
starting at row 38 my goal is to get the solver to solve each row separately. so the second sim would be to maximize F39 using P39:AQ39. then maximize F40 next and so on. what i need to get is a record I38:N38 of the players used somehow and not just the maximum points. this record is important. would i need to move the rank column (currently F5:F32) to a new place for each sim? just trying to get a solver going for 100 sims to see how it works and if it is feasible right now. i included some screen shots alf. im gonna have a crack at trying to loop it today but any help appreciated. not sure if i will have much success with the loop or vba stuff.

[Alf]

Hi
Hope I have not misled you as you don't need OpenSolver as long as the number of constraints are equal to or less than 200. If so the nornal excel solver will do nicely.

To run solver in a loop with vba you need to set a reference to solver. Click the "Visual Basic" icon -> "Tools" -> "References" and tick box marked Solver.

How to ser up a solver loop macro just search forum for solver + macro.

To run OpenSolver with a macro you will have add a reference to this as well. For your macro you can use a solver macro except that the line

Please Login or Register  to view this content.

must be replaced by the command line to run OpenSolver.

Don't remember it but there is a thread in the forum by poster handa and thread name is "The best chosen various lenghts for the least reminder" and in post 25 I've uploaded a file where macro runs OpenSolver. So macro contains model building for the excel solver + the run command for OpenSolver this is why a reference must be set to both solvers for macro to work.

Alf

[Alf]

Thanks for feedback and rep. And yes I do agree with you that knowing about these two solvers gives you the freedom of choice as a limit of 200 constraints in real life gets eaten up rather quickly.

For the homework kind of solver problems that get posted in the forum the excel solver is sufficient but for big linear problems OpenSolver is a handy tool unless one work for a company willing to pay ~ 3500 $ for the really advanced solver models.

Alf

[maxpower2]

hi alf. if you look at the pink bit highlighted in the screen shot. will i need to make a column or row free for the solver to change for every sim? is that how i would get a record of which 6 players it chooses? do i need to get the solver to adjust a new set of variable cells each time. that way i would have a record of the 6 players chosen. if i dont will the result i get just be the top score? as everytime a new sim is done the solver would wipe the old selection and replace with new ones? am i thinking about this right? is the a better way? thanks

[Alf]

Let me see if I get this right. The range of cells to change is F5:F32 so after the first run do the C5:E32 get up-dated with completely new values and find the new 6 persons that meets the criteria or do you wish to find the next lot of 6 persons (2nd best lot) that meet criteria of max sim and costs?

In the latter case I would probably use an autofilter and filter for the value 1 in the F column. Then copy the filtered range C?:F? to another worksheet and then delite the filtered range and loop solver for another run. Deleting row in a solver model will normally adjust the model to the new conditions. Range cells to change will adjust as well as target cell.

Since your uploaded image have 28 rows of data looping 4 times should be sufficient i.e. 6 * 4 = 24.

As I'm still limited to using a smartphone I can't upload a file but at the end of next week I'm back and then I have access to a PC.

Alf

[maxpower2]

hi alf

C5:E32 will keep the same values.

and find the new 6 persons that meets the criteria or do you wish to find the next lot of 6 persons (2nd best lot) that meet criteria of max sim and costs?

the most important is 1st place. but if it is possible to return 2nd, 3rd or more or that is very helpful and improves the results. the best 2nd place team is allowed to include all the players. so can pick from all 24 players just like the 1st place team did. maybe a constraint would work to pick a team on less points the the first result. so for looping its the maximum we can get away with. i can deal with it being slow if we are getting lots of data back.

the auto filter sounds good , makes sense to me. but just F5:F32 as the rest are constants.

cantosh posted an alternative method last night here. this returns the winner for each row. so i actually have a working model now that i can use. on my computer this solution is instant for 100 sims. but for say 20,000 it slows to maybe 25 to 30 minutes. any solution which could include 2nd, 3rd or a set amount of places for each sim would be incredibly valuable and a big goal. maybe a solver solution would improve the speed or enable a larger number of simulations.

https://www.excelforum.com/excel-gen...readsheet.html

[Alf]

2nd place team is allowed to include all the players.

That should be doable by first running solver the way it's setup in #post 10 and then add a solver loop where there is a constrain added i.e. the max points on a solver run should be equal to or less than 90 % of the previous run.

Will have a go at building a solver model for this when I get back 26 - 28 May.

Alf

[maxpower2]

that would be great. appreciate the help. i will add a couple more constraints and post before then. i had some constraints which enabled me to leave a player out of the calculations. the ones you mentioned to me in a previous post and gave a method to simplify. im not really sure what solvers are capable of. if it was practical to have an option to decide how many places to collect data per sim would be usefull. 2nd, 3rd or 10th etc.

[maxpower2]

i added some constraints W3:AC32 , this is used to remove a match up. usually it means it is not taking place so the scores shouldn't be considered.

[Alf]

I've set up a solver model that start with a "normal" run and then adds a "For Loop" for the next runs (at the moment only 4 but change the i value to suit your needs).

I've tested using both the excel solver as well as the OpenSolver. Both takes 59 seconds on my PC to finish all 5 runs (1 + 4 loops). The result differ a bit so
have a look at the sheet "result_solver_open_solver" to see what setup you prefer.

In order to run the macro "solver_loop" you need to set a reference to solver in Visual Basic. If you decide you wish to use the OpenSolver you must set a reference
to both solver and OpenSolver as the model is build using solver commands but the solving run command is fired by OpenSolver.

If you decide to use OpenSolver you must replace the line

Please Login or Register  to view this content.

with the command

Please Login or Register  to view this content.

After the first run macro sets an autofilter and copies the filtered result to sheet "result", it then takes the object value cell C38 and multiplies this value
with .9 and add a constraint to the solver model that the next C38 value should be equal to or less than this value.

The result sheet is cleared every time the macro is started.

Alf

[maxpower2]

thanks for this alf. will take a look later today.

[maxpower2]

hi alf. thanks for setting that up. seems to be working well. just trying to learn how to make some adjustments. i think i kinda understand. i got the solver running from macro. in the snip i would change the 0.9 to 0.9999 to get the 2nd best yeah? think i saw 0.9 twice to change in the visual basic. and i can change that 5 to any number? and then to get the top 5 from the next sim i just change the objective cell to C39? is that how its set up? or does it move to C39 automatically

[Alf]

snip i would change the 0.9 to 0.9999 to get the 2nd best yeah?

You can of course set the value to 0.9999 but the difference between the first run and the second run may then be so small that you may get the same value again. Perhaps the better option would be to say that the first run is the optimal run and the subsequent runs should at least be 10 pts lower than the previous run. To model that you could do like this:

Please Login or Register  to view this content.

Then there is also the question of how you define second best. To me that that would be to set up a team with the best players to get the max points within a given budget, having found that I would then remove these players an do another run with the reaming one to find the second best team.

Could also be that having found the best team I would lower my budget with 10 % and see what max point that would buy me and call that the second best team.

think i saw 0.9 twice to change in the visual basic.

Yes that's correct, this solver loop contains two part. First a run where you get the best possible pts result and there is no restrain on the pts but marco set a pts value that is
90 % of the maximum pts solver find at the first run.

Then there comes the solver loop where i goes from i = 1 to 5 and for the first run in this loop there is a constraint on the pts value, it should be equal to or less than first pts value (i.e. 593.2687) so when solver finds this new value it takes 90 % of that and write that result cell B41 in order to prepare for the run where i = 2 with a new constraint on the pts value.

and i can change that 5 to any number?

Yes that's correct but as i is dim'd as "Integer" it must be an integer number.

hen to get the top 5 from the next sim i just change the objective cell to C39? is that how its set up? or does it move to C39 automatically

Not sure I understands that, C38 is the objective cell containing the formula

Please Login or Register  to view this content.

and the C39 is just the sum of binaries to ensure that 6 persons are selected. Can you clarify?

Alf

[maxpower2]

hi alf. im finding it really useful. although im actually using it a little different to i initially intended. i set the wrong objective cell. it should of been F38 not C38. and then i was hoping to move the objective cell down column F to F39, F40 etc. what i wrote above about cell C38 and then C39 doesn't make sense. i thought i was talking about F38 and F39.

i plugged in some different data so i could still use your solver loop as it is currently (i used some average numbers rather than the results of 1 sim). working well and i managed to tweak the visual basic like above which is v helpful.

[Alf]

Thanks for feedback and rep

You can of course keep the objective cell as C38 but add a constraint where C38 should be equal to or less than F38 and put this in a loop i.e.

Please Login or Register  to view this content.

But as the result in range F38:F137 changes with every change in the range F5:F32 solver will behave like a puppy chasing its own tail running around in circles never reaching a solution.
If you wish to test this I would recommend using a narrow range "For i = 38 To 40" just to see the effect of this constraints.

Alf

[maxpower2]

there were some constraints that i forgot to add as well. i wrote them on the sheet but forgot to add in the solver. they work in the solver when i add them but not in the solver loop. then the constraints keep disappearing for some reason. im not sure how to add them into the solver loop. i have uploaded a picture of the constraints im trying to add. will the record feature work for this? i haven't used it before tho. thanks

[Alf]

As I'm not sure of what macro you use for the solver loop your problem could be caused by the fact that the original macro contains of two parts.

A solver run once to find maximum pin value and a number of subsequent runs where pin value should be equal to or less than the previous pin value.

After every solver run the command "SolverReset" is called and this clears the solver setup in order to avoid buildup on solver settings so if the constraint was added only in the first solver run but not in "For i = 1 To 4" part solver loop this constraint will be deleted and when you checks the solver setup after the macro has run you will see the solver setup for the last loop.

So to include new constraint in the original macro you have to modify it like this:

Please Login or Register  to view this content.

Alf