x Turn on thread page Beta
 You are Here: Home >< Maths

# Trouble with linear programming in excel solver watch

1. (Original post by mamaH)
Thanks Ghostwalker
I only find the solution when I remove the minimum recommended energy constraint (3). I guess I have over-constrained my model.

I have changed my variable cells to C41 : C69. Please see the attached. However i do not understand your comment on allowing G41:G69 to vary and also C41:C69. I there a way of allowing both these cells to vary?

Thanks again
I think if you just separate the two lists of cells with a comma, e.g. C41 : C69,G41: G69
If you're highlighting the cells rather than typing, you may need to hold down the "Ctrl" key for the second block - at least that's the way it works on the verision I'm using.

As I understand what you have, C41 : C69 are the optimal amounts that you are varying in order to mimimise your sum of absolute relative deviations.

G41 : G69, which form the basis of your objective function are not the relative absolute deviations as such, but via their constraints they are above the relative absolute deviations. Minimising them pulls them down to the relative absoute deviations, which is what you want, and why they need to vary.

Edit: If you can get the above sorted, constraint (3) might not be an issue. At the moment however you've got two lots of processing that don't tie together. Also, I'd expect the processing to take considerably longer once you've made the changes.
2. Hi, this has worked fine. thanks for your help

Why do you say I have two lots of processing that don't tie together? the first version is the one that I did wrong and the second one was with the corrections. I am attaching here the third one which had worked just fine.

You saved my head. Thank you
3. (Original post by mamaH)
Hi, this has worked fine. thanks for your help

Why do you say I have two lots of processing that don't tie together? the first version is the one that I did wrong and the second one was with the corrections. I am attaching here the third one which had worked just fine.

You saved my head. Thank you
Can't see the third set.

My understanding is that if you only vary the C41: C69 set of variables, you can't change the G41: G69 set as they're constants, and so G70 will always be zero.

You're then forcing the LPmodel to satisfy all the constraints with G70=0, which is not feasible.
4. Okay. I got it now. I guess it failed to attach. please find it attached here
hope I got it this time
5. Hi,

Thanks for your response. Yes I am optimising E70 which is the sum of relative deviations from the mean ... m-X/m. The objective function itself should be in absolute value i.e. minimise the sum of the absolute values of relative deviations from the mean ∑|m-X|/m since the simple relative deviation might be positive or negative.

As to the constraints:
(1) C41:C69<=F5:F33 i.e. optimal energy intake value should not exceed maximum energy limit
(2) C41:C69>=E533 i.e. optimal energy intake value should not be less than minimum energy limit
(3) C70>=C73 i.e. the minimum recommended energy allowance
(4) C77:C79<=E7779 i.e. total energy value from items in this food group not to exceed maximum energy required from this group
(5) C77:C79>=E7779 i.e. total energy value from items in this food group not to be less than minimum energy required from this group
(6) H70<=1200 i.e. total expenditure on food not to exceed 1200 Tanzanian shillings (equivalent to half a euro)
(7) I41:I69<=D533 i.e. the total food portion not to exceed the maximum daily limit

these below are for the solution you had suggested on linearising the absolute value of the objective function
(8) E4169>=F41:F69 this is the negative version of the objective
(9) E4169>=G41:G69 this is the negative version of the objective

I am running excel solver for MAC 2011. When I do remove the recommended allowance contraint
6. every time I try to attach t fails
Attached Images

7. (Original post by mamaH)
every time I try to attach t fails
Bit of a mixture of posts there.

However, I assume you have everything sorted now as per one of your previous posts.
8. I do, I just would like you to see how it looks like so I can be confident enough that it is the right thing
Attached Images

9. (Original post by mamaH)
I do, I just would like you to see how it looks like so I can be confident enough that it is the right thing
Will check properly in an hour or two - just having lunch, etc.
10. (Original post by mamaH)
I do, I just would like you to see how it looks like so I can be confident enough that it is the right thing
OK, constraints look good.

Obviously, I can't see the results of the optimisation.
11. Attached is the solution
Attached Images

12. here is the solution
Attached Images

13. Here is the excel format. i just realised I have sent a picture
Attached Files
14. optimisation problem.xlsx (61.7 KB, 44 views)
15. (Original post by mamaH)
Here is the excel format. i just realised I have sent a picture
Bit puzzled as to why one of the inequalities is showing as false, but will have to wait until tomorrow now, as I'm just out for the night.
16. Thank you. I was also wondering the same but when I look at the actual values I see it's true E41: E69 <= G41 : G69
17. (Original post by mamaH)
Thank you. I was also wondering the same but when I look at the actual values I see it's true E41: E69 <= G41 : G69
With the precision currently displayed it appears true, but if you increase the number of decimal places to 20 say, then you'll see the discrepancies, and the "FALSE" report is accurate, in particular rows 41, 44, 57, 62, 64, and 66.

If you're not happy with the discrepancies, you could tweek the parameters in Solver for the number of iterations, or accuracy, or time. Can't really help with that.

Rest looks OK.
18. I am not very familiar with using excel solver. I don't understand when you say I could tweek the parameters in Solver for the number of iterations, or accuracy, or time. can you please explain this
19. should I increase or decrease the number of iterations, accuracy and/or time?
20. (Original post by mamaH)
should I increase or decrease the number of iterations, accuracy and/or time?
Increase, and see if it makes any difference.

Can't say more as I don't know, and I can't run my version of the software against your data to check. It's a case of experimenting and see what happens.

Note: The discrepencies are very small at the moment and it may not be possible to eliminate them entirely due to rounding and precision of arithmetic.
21. HI AGAIN.
I have tried varying the expenditure constraint to 1250 and the values are true. I am a bit confused as to why the last value gave a false. can you please help e explain this? I might need it in my report.

Thanks
Attached Files
22. optimisation problem.xlsx (48.7 KB, 52 views)

TSR Support Team

We have a brilliant team of more than 60 Support Team members looking after discussions on The Student Room, helping to make it a fun, safe and useful place to hang out.

This forum is supported by:
Updated: June 14, 2015
Today on TSR

Poll
Useful resources

### Maths Forum posting guidelines

Not sure where to post? Read the updated guidelines here

### How to use LaTex

Writing equations the easy way

### Study habits of A* students

Top tips from students who have already aced their exams