Join TSR now and get all your revision questions answeredSign up now

Trouble with linear programming in excel solver Watch

    • Study Helper
    Offline

    13
    (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.
    Offline

    0
    ReputationRep:
    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
    • Study Helper
    Offline

    13
    (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.
    Offline

    0
    ReputationRep:
    Okay. I got it now. I guess it failed to attach. please find it attached here
    hope I got it this time
    Offline

    0
    ReputationRep:
    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
    Offline

    0
    ReputationRep:
    every time I try to attach t fails
    Attached Images
     
    • Study Helper
    Offline

    13
    (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.
    Offline

    0
    ReputationRep:
    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
     
    • Study Helper
    Offline

    13
    (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.
    • Study Helper
    Offline

    13
    (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.
    Offline

    0
    ReputationRep:
    Attached is the solution
    Attached Images
     
    Offline

    0
    ReputationRep:
    here is the solution
    Attached Images
     
    Offline

    0
    ReputationRep:
    Here is the excel format. i just realised I have sent a picture
    Attached Files
  1. File Type: xlsx optimisation problem.xlsx (61.7 KB, 36 views)
    • Study Helper
    Offline

    13
    (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.
    Offline

    0
    ReputationRep:
    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
    • Study Helper
    Offline

    13
    (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.
    Offline

    0
    ReputationRep:
    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
    Offline

    0
    ReputationRep:
    should I increase or decrease the number of iterations, accuracy and/or time?
    • Study Helper
    Offline

    13
    (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.
    Offline

    0
    ReputationRep:
    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
  2. File Type: xlsx optimisation problem.xlsx (48.7 KB, 39 views)
 
 
 
Poll
Which Fantasy Franchise is the best?
Useful resources

Make your revision easier

Maths

Maths Forum posting guidelines

Not sure where to post? Read the updated guidelines here

Equations

How to use LaTex

Writing equations the easy way

Student revising

Study habits of A* students

Top tips from students who have already aced their exams

Study Planner

Create your own Study Planner

Never miss a deadline again

Polling station sign

Thinking about a maths degree?

Chat with other maths applicants

Can you help? Study help unanswered threads

Groups associated with this forum:

View associated groups

The Student Room, Get Revising and Marked by Teachers are trading names of The Student Room Group Ltd.

Register Number: 04666380 (England and Wales), VAT No. 806 8067 22 Registered Office: International House, Queens Road, Brighton, BN1 3XE

Quick reply
Reputation gems: You get these gems as you gain rep from other members for making good contributions and giving helpful advice.