excel solver help

[alirulez]

Hi

By way of background:

I am working with the Excel Solver to find the optimal fantasy football roster (maximum projected points). The constraints are:

- Salary Cap of $100,000
- Roster Constraints:
- 1 QB
- 2 to 4 RB
- 2 to 4 WR
- 1 to 3 TE
- 1 Defense
- Total roster size must be 10

I have set up the spreadsheet so that Column A will either be 1 or 0 - 1 being that that player is selected. I have built the constraints into the solver, but it is not working correctly for some reason. Are you able to help?


Thanks very much!

[twiggywales]

i cant see where it isnt working tbh.

can you show in more detail as it seems ok

[alirulez]

When I run the solver, the solution it finds sets the variable cells (A2:A201 range) to value '0.05'
In reality I would've expected that the solver returns 10 cells with '1' values and the rest with '0' values, based on the constraints

[Alf]

In you formula you are using "SUMIF" and "COUNTIF" but solve can't handle discontinuous functions like "IF", "COUNTIF" and "SUMIF"

You could substitue formula in C203 "=SUMPRODUCT(A2:A201,C2:C201)" and also use "SUMPRODUCT" in D203.

The number of players is quite big, to speed up things in Solver go "Options" and tick "Assume Liniar Model" and "Assume Non-Negative"

You need to change the constraint in range H203 to H207 since they all contain "COUNTIF" statement. If you just want to test Solver, just remove them form Solver and do a run.

Not sure but I think that the "Experimental Solver" found in Excel 2010 may be able to cope with discontinuous functions.

Alf

[alirulez]

Alf

Thanks for that - I understand where you are coming from.

Do you know of a substitute formula I could use on cells H203 to H207?

Thanks very much!

In you formula you are using "SUMIF" and "COUNTIF" but solve can't handle discontinuous functions like "IF", "COUNTIF" and "SUMIF"

You could substitue formula in C203 "=SUMPRODUCT(A2:A201,C2:C201)" and also use "SUMPRODUCT" in D203.

The number of players is quite big, to speed up things in Solver go "Options" and tick "Assume Liniar Model" and "Assume Non-Negative"

You need to change the constraint in range H203 to H207 since they all contain "COUNTIF" statement. If you just want to test Solver, just remove them form Solver and do a run.

Not sure but I think that the "Experimental Solver" found in Excel 2010 may be able to cope with discontinuous functions.

Alf

[Alf]

Build a model for you using the "SUMPRODUCT" function.

Be warned that it takes a bit of time to solve your model as 200 rows seems to be a bit much for the Excel solver.

Alf

PS When answering to post don't quote all of earlier postings it just clutters up the thread

[alirulez]

Alf
much appreciated and thanks for your patience

I ran the model you sent across through Solver, but it failed with an error message on the constraints. I had to change the references from the H column to the I column. I also added in the minimum figures, but now solver falls over and says there is no feasible solution!

Any ideas?

[Alf]

I'm just as pussled as you because when first I tested it it work ok. When I cheked my model I saw the I've messed up the constrain so I removed my upload and tried again but as of now I also get "no feasible solution".

Could you rebuild the file again? As you have seen the formulas you don't need using "countif" and "sumif". My excel 2007 recenly crashed working with this file so I'll try to rebuild it in Excel 2003 and see if that works better.

Alf

[Alf]

No can do, trying to run Solver it fells flat on it's feet and the only explanation I can think of is that Excel hogs all my memory. Strange as I got 8 GHz of RAM.

I've made a modification to the model, one of the reasons Excel gives up could be the formulas like "=SUMPRODUCT((E2:E201="QB")*(A2:A201=1))"

When excel runs it tests with values between 0 and 1 so it's difficult to fulfil the SUMPRODUCT criteria. Perhaps a change to (A2:A201=<1) would work better?

I've uploaded my Excel 2003 file. See if you can run it in an Excel 2007 environment.

Alf

[Alf]

Last update. Got an optimal solution after "slimming" you model from 201 rows down to 113 using my upload. So you can probably increase the row number a bit more but how much I can't tell. You just have test to find the maximum number of rows Solver can handle.

Alf

[alirulez]

Thanks Alf.. your help has been invaluable. I'll keep on experimenting with your model as the base.

Cheers!

[Alf]

After playing around a bit with your file I found the maximum (on my PC using excel 2007) number of rows to be 138.

Limiting factor seems to be memory. Checking the different models I also found that using the “SUMPRODUCT” formula when calculating “QB”, “RB”, “WR”, “TE” and “Defense” made the model less robust as excel could only handle 130 rows before collapsing.

So even if the SUM setup for “QB”, “RB” and so forth don’t look so impressive it is the more “robust” solution to your problem as it can handle more rows.

If this solves your problem could you please mark your thread solved and if you like to give a rating to my answer click on the small star bottom left in my post.

Alf

[MohsinWaqas]

Hi,
I have data of x and y and the releation between x and y is

y=ax+b

and I want to estimate the value of a and b.

the data is given below

x= 2,4,6,8,10,13,15 y=22,25,28,33,36,39,43

how can i find the value of a and b using solver.

[FDibbins]

MohsinWaqas welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.