Hi, I am quite new here so I am not exactly sure how everything works. I just wanted to know if anyone could help me with Solver?
-Given some information, I need to construct a spreadsheet and use Solver to resolve a constrained optimization problem (may be simple for some but it is my first time doing this sort of thing).
-I need to construct the final/correct version of this spreadsheet to prepare for my upcoming test (very soon!)
-Until now I have prepared a spreadsheet and added the constraints but my solver takes forever to solve.
-Also, the thing I need to maximize (wellbeing) never reaches the correct result (approx. 11760) which was given (but not mentioned in the images below). This is most likely due to some wrong or missing input or constraint. Other than that, I do not know what is wrong or how I can improve it from this point (the sheet may be a bit unorganized, so I guess that needs to be worked on too).
-However, I have not been able to get any result after I added integer constraints, which are required! It solves for hours and never reaches the result?
Here is the given information:
Here is the Spreadsheet that I constructed:
All the above hours should be integers (some are formatted as 0 d.p so you cannot view the decimals, but if you hover over it you can see that they are not integers).
Here are my inputs into Solver (according to the scenario above):
Overall, my main problem is the time that solver is not able to resolve the constrained optimization problem (as I have left it running for more than an hour and still have not gotten any result)!
How can I solve this and how can I reach correct results?
Any help would be greatly appreciated.
Thank you!
-Given some information, I need to construct a spreadsheet and use Solver to resolve a constrained optimization problem (may be simple for some but it is my first time doing this sort of thing).
-I need to construct the final/correct version of this spreadsheet to prepare for my upcoming test (very soon!)
-Until now I have prepared a spreadsheet and added the constraints but my solver takes forever to solve.
-Also, the thing I need to maximize (wellbeing) never reaches the correct result (approx. 11760) which was given (but not mentioned in the images below). This is most likely due to some wrong or missing input or constraint. Other than that, I do not know what is wrong or how I can improve it from this point (the sheet may be a bit unorganized, so I guess that needs to be worked on too).
-However, I have not been able to get any result after I added integer constraints, which are required! It solves for hours and never reaches the result?
Here is the given information:
Here is the Spreadsheet that I constructed:
| 10722273workbook ES2.xlsx | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | ||||||||
| 2 | Course | A | B | C | ||||
| 3 | No study score | 30 | 25 | 10 | ||||
| 4 | Score per hour | 3.1 | 3.4 | 2.1 | ||||
| 5 | WorkPweek | 19 | 18 | 29 | ||||
| 6 | Aimed score | 60 | 60 | 60 | ||||
| 7 | score | 60 | 60 | 60 | ||||
| 8 | ||||||||
| 9 | Income per hour | $11.20 | ||||||
| 10 | Hours | 15.71 | Rent | Grocery | Left | |||
| 11 | Net income | 175.97028 | 75 | 50 | 50.970276 | |||
| 12 | Spending | Leisure | Excersize | |||||
| 13 | Cost | $10.00 | $4.00 | |||||
| 14 | Time | 4 | 2 | total | left | |||
| 15 | total | 42.364705 | 8.6055706 | 50.970276 | 7.207E-08 | |||
| 16 | Sleep | 6 | left | |||||
| 17 | sleep tgt | 6 | 0 | Time | ||||
| 18 | Wellbeing | 551.77275 | leisure+EXCER | 44.715042 | ||||
| 19 | Study | 65.573326 | ||||||
| 20 | Sleep | 42 | ||||||
| 21 | work | 15.71 | ||||||
| 22 | total | 168 | ||||||
| 23 | left | 0 | ||||||
| 24 | max | 168 | ||||||
Sheet1 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B7:D7 | B7 | =B5*B4 |
| B11,B15:C15 | B11 | =B10*B9 |
| E11 | E11 | =B11-C11-D11 |
| D15 | D15 | =C15+B15 |
| E15 | E15 | =E11-D15 |
| C17 | C17 | =B16-B17 |
| B18 | B18 | =B7^0.3*C7^0.4*D7^0.5*B16^0.5*B14^0.9*C14^0.4*B10^-0.4 |
| F18 | F18 | =+(B14+C14)*7 |
| F19 | F19 | =SUM(B5:D5) |
| F20 | F20 | =B16*7 |
| F21 | F21 | =B10 |
| F22 | F22 | =SUM(F18:F21) |
| F23 | F23 | =F24-F22 |
| F24 | F24 | =7*24 |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| solver_adj | =Sheet1!$B$5:$D$5,Sheet1!$B$10,Sheet1!$B$14:$C$14,Sheet1!$B$16 | F19, B7 |
| solver_lhs1 | =Sheet1!$B$10 | F21, B18, B11 |
| solver_lhs2 | =Sheet1!$B$14:$C$14 | F18, B18, B15 |
| solver_lhs3 | =Sheet1!$B$16 | F20, C17, B18 |
| solver_lhs4 | =Sheet1!$B$5:$D$5 | F19, B7 |
| solver_lhs5 | =Sheet1!$B$7:$D$7 | B18 |
| solver_lhs6 | =Sheet1!$B$7:$D$7 | B18 |
All the above hours should be integers (some are formatted as 0 d.p so you cannot view the decimals, but if you hover over it you can see that they are not integers).
Here are my inputs into Solver (according to the scenario above):
Overall, my main problem is the time that solver is not able to resolve the constrained optimization problem (as I have left it running for more than an hour and still have not gotten any result)!
How can I solve this and how can I reach correct results?
Any help would be greatly appreciated.
Thank you!
