Solver not working
in [Charts]
|
Prev: Show decreases and increases
Next: Charting Question
From: Kerry on 1 Dec 2009 16:53 I have 4 cells that Solver is supposed to change to minimize the value of one target cell, where the target cell sums a bunch of rows that have changed values depending on the 4 aforementioned cells. Whether I click min or max in solver, it says it solves the equation fully but the results do not change for min or max compared to the pre- solver values. To test if it was working, I (in short) click for the solver to find an exact target value I know for a fact exists locally (and required only changing one cell value), but Solver says it cannot find a feasible solution. All of this tells me that the proper operations or operation sequence is not happening during the execution of solver. I thought I'd fixed the issue at first, when I rearranged the involved formulas so that everything was on the same Excel sheet, but it didn't work. Are there other Solver limitations I need to know about that could be causing the issue? I was surprised that Microsoft Support and the Solver Help file did not mention that I need to have all formula references on the same tab, and so am concerned there's other limitations I am not aware of. I have tried changing all Solver Options too, but no help. I will try to simplify the involved data below: Solver changes these constants... Cell A2 = 0.31 Cell A3 = 0.25. Cell A4 = 0.67 Cell A5 = 0.52 to minimize the value of a target cell with the formula: =SUM (D41:E2000,I41:J2000,N41:O2000,S41:T2000,X41:Y2000,AC41:AD2000,AH41:AI2000,AM41:AN2000,AR41:AS2000,AW41:AX2000,BB41:BC2000) The columns summed in the target cell formula above have their own formulas. An example would be: =IF(AND(C41<A$2,C41<((A$6*B41)+A$9)),1,0), where C41 and B41 are constants I am not changing in solver, A$2 is one of the constants I am changing in solver, and A$6 and A$9 have formulas that reference some of the constants I am changing but are not directly inputted to the solver. Thus, solver changing any of A$2 through A$5, will change A$9 and/or A $6, which in turn changes the column values that are summed in the target cell formula, thus changing the target cell value. Thanks for any help, K
From: Bernard Liengme on 1 Dec 2009 21:24 You cannot have conditional functions within a Solver model So the problem seems to be the IF statements best wishes -- Bernard Liengme http://people.stfx.ca/bliengme Microsoft Excel MVP "Kerry" <kbrownk(a)gmail.com> wrote in message news:d108479c-726f-42e6-83f0-98e9e65f976e(a)v37g2000vbb.googlegroups.com... > I have 4 cells that Solver is supposed to change to minimize the value > of one target cell, where the target cell sums a bunch of rows that > have changed values depending on the 4 aforementioned cells. > > Whether I click min or max in solver, it says it solves the equation > fully but the results do not change for min or max compared to the pre- > solver values. To test if it was working, I (in short) click for the > solver to find an exact target value I know for a fact exists locally > (and required only changing one cell value), but Solver says it cannot > find a feasible solution. > > All of this tells me that the proper operations or operation sequence > is not happening during the execution of solver. I thought I'd fixed > the issue at first, when I rearranged the involved formulas so that > everything was on the same Excel sheet, but it didn't work. > > Are there other Solver limitations I need to know about that could be > causing the issue? I was surprised that Microsoft Support and the > Solver Help file did not mention that I need to have all formula > references on the same tab, and so am concerned there's other > limitations I am not aware of. > > I have tried changing all Solver Options too, but no help. > > I will try to simplify the involved data below: > > Solver changes these constants... > Cell A2 = 0.31 > Cell A3 = 0.25. > Cell A4 = 0.67 > Cell A5 = 0.52 > > to minimize the value of a target cell with the formula: > =SUM > (D41:E2000,I41:J2000,N41:O2000,S41:T2000,X41:Y2000,AC41:AD2000,AH41:AI2000,AM41:AN2000,AR41:AS2000,AW41:AX2000,BB41:BC2000) > > The columns summed in the target cell formula above have their own > formulas. An example would be: > =IF(AND(C41<A$2,C41<((A$6*B41)+A$9)),1,0), > where C41 and B41 are constants I am not changing in solver, A$2 is > one of the constants I am changing in solver, and A$6 and A$9 have > formulas that reference some of the constants I am changing but are > not directly inputted to the solver. > > Thus, solver changing any of A$2 through A$5, will change A$9 and/or A > $6, which in turn changes the column values that are summed in the > target cell formula, thus changing the target cell value. > > Thanks for any help, > K
From: Kerry on 2 Dec 2009 00:07 On Dec 1, 9:24 pm, "Bernard Liengme" <blien...(a)stfx.TRUENORTH.ca> wrote: > You cannot have conditional functions within a Solver model > So the problem seems to be the IF statements > best wishes > -- > Bernard Liengmehttp://people.stfx.ca/bliengme > Microsoft Excel MVP > > "Kerry" <kbro...(a)gmail.com> wrote in message > > news:d108479c-726f-42e6-83f0-98e9e65f976e(a)v37g2000vbb.googlegroups.com... > > > I have 4 cells that Solver is supposed to change to minimize the value > > of one target cell, where the target cell sums a bunch of rows that > > have changed values depending on the 4 aforementioned cells. > > > Whether I click min or max in solver, it says it solves the equation > > fully but the results do not change for min or max compared to the pre- > > solver values. To test if it was working, I (in short) click for the > > solver to find an exact target value I know for a fact exists locally > > (and required only changing one cell value), but Solver says it cannot > > find a feasible solution. > > > All of this tells me that the proper operations or operation sequence > > is not happening during the execution of solver. I thought I'd fixed > > the issue at first, when I rearranged the involved formulas so that > > everything was on the same Excel sheet, but it didn't work. > > > Are there other Solver limitations I need to know about that could be > > causing the issue? I was surprised that Microsoft Support and the > > Solver Help file did not mention that I need to have all formula > > references on the same tab, and so am concerned there's other > > limitations I am not aware of. > > > I have tried changing all Solver Options too, but no help. > > > I will try to simplify the involved data below: > > > Solver changes these constants... > > Cell A2 = 0.31 > > Cell A3 = 0.25. > > Cell A4 = 0.67 > > Cell A5 = 0.52 > > > to minimize the value of a target cell with the formula: > > =SUM > > (D41:E2000,I41:J2000,N41:O2000,S41:T2000,X41:Y2000,AC41:AD2000,AH41:AI2000,AM41:AN2000,AR41:AS2000,AW41:AX2000,BB41:BC2000) > > > The columns summed in the target cell formula above have their own > > formulas. An example would be: > > =IF(AND(C41<A$2,C41<((A$6*B41)+A$9)),1,0), > > where C41 and B41 are constants I am not changing in solver, A$2 is > > one of the constants I am changing in solver, and A$6 and A$9 have > > formulas that reference some of the constants I am changing but are > > not directly inputted to the solver. > > > Thus, solver changing any of A$2 through A$5, will change A$9 and/or A > > $6, which in turn changes the column values that are summed in the > > target cell formula, thus changing the target cell value. > > > Thanks for any help, > > K I see. Any suggestions for a workaround? I kind of need those IF statements. Thanks again, K
From: Bernard Liengme on 2 Dec 2009 11:52 Check will www.solver.com. Maybe one of the premium version will work best wishes "Kerry" <kbrownk(a)gmail.com> wrote in message news:66a8c528-3c40-4eec-b5c5-1277bdbac42c(a)j4g2000yqe.googlegroups.com... > On Dec 1, 9:24 pm, "Bernard Liengme" <blien...(a)stfx.TRUENORTH.ca> > wrote: >> You cannot have conditional functions within a Solver model >> So the problem seems to be the IF statements >> best wishes >> -- >> Bernard Liengmehttp://people.stfx.ca/bliengme >> Microsoft Excel MVP >> >> "Kerry" <kbro...(a)gmail.com> wrote in message >> >> news:d108479c-726f-42e6-83f0-98e9e65f976e(a)v37g2000vbb.googlegroups.com... >> >> > I have 4 cells that Solver is supposed to change to minimize the value >> > of one target cell, where the target cell sums a bunch of rows that >> > have changed values depending on the 4 aforementioned cells. >> >> > Whether I click min or max in solver, it says it solves the equation >> > fully but the results do not change for min or max compared to the pre- >> > solver values. To test if it was working, I (in short) click for the >> > solver to find an exact target value I know for a fact exists locally >> > (and required only changing one cell value), but Solver says it cannot >> > find a feasible solution. >> >> > All of this tells me that the proper operations or operation sequence >> > is not happening during the execution of solver. I thought I'd fixed >> > the issue at first, when I rearranged the involved formulas so that >> > everything was on the same Excel sheet, but it didn't work. >> >> > Are there other Solver limitations I need to know about that could be >> > causing the issue? I was surprised that Microsoft Support and the >> > Solver Help file did not mention that I need to have all formula >> > references on the same tab, and so am concerned there's other >> > limitations I am not aware of. >> >> > I have tried changing all Solver Options too, but no help. >> >> > I will try to simplify the involved data below: >> >> > Solver changes these constants... >> > Cell A2 = 0.31 >> > Cell A3 = 0.25. >> > Cell A4 = 0.67 >> > Cell A5 = 0.52 >> >> > to minimize the value of a target cell with the formula: >> > =SUM >> > (D41:E2000,I41:J2000,N41:O2000,S41:T2000,X41:Y2000,AC41:AD2000,AH41:AI2000,AM41:AN2000,AR41:AS2000,AW41:AX2000,BB41:BC2000) >> >> > The columns summed in the target cell formula above have their own >> > formulas. An example would be: >> > =IF(AND(C41<A$2,C41<((A$6*B41)+A$9)),1,0), >> > where C41 and B41 are constants I am not changing in solver, A$2 is >> > one of the constants I am changing in solver, and A$6 and A$9 have >> > formulas that reference some of the constants I am changing but are >> > not directly inputted to the solver. >> >> > Thus, solver changing any of A$2 through A$5, will change A$9 and/or A >> > $6, which in turn changes the column values that are summed in the >> > target cell formula, thus changing the target cell value. >> >> > Thanks for any help, >> > K > > I see. Any suggestions for a workaround? I kind of need those IF > statements. > > Thanks again, > K
From: Tushar Mehta on 2 Dec 2009 12:09
I haven't read your post in detail but a long time ago I shared several tips on how to convert various non-linear criteria into linear ones. See my posts in http://groups.google.com/group/microsoft.public.excel.programming/browse_thread/thread/66d36e97de2345a7/a5f16683d9c0b0d5?q=group:*excel.*+insubject:Identify+insubject:the+insubject:cells+insubject:that+insubject:add+insubject:up+insubject:to+insubject:zero+author:tushar#a5f16683d9c0b0d5 If I can count correctly, the 3rd post by me shows how to "linearize" a IF condition. On Tue, 1 Dec 2009 13:53:45 -0800 (PST), Kerry <kbrownk(a)gmail.com> wrote: >I have 4 cells that Solver is supposed to change to minimize the value >of one target cell, where the target cell sums a bunch of rows that >have changed values depending on the 4 aforementioned cells. > >Whether I click min or max in solver, it says it solves the equation >fully but the results do not change for min or max compared to the pre- >solver values. To test if it was working, I (in short) click for the >solver to find an exact target value I know for a fact exists locally >(and required only changing one cell value), but Solver says it cannot >find a feasible solution. > >All of this tells me that the proper operations or operation sequence >is not happening during the execution of solver. I thought I'd fixed >the issue at first, when I rearranged the involved formulas so that >everything was on the same Excel sheet, but it didn't work. > >Are there other Solver limitations I need to know about that could be >causing the issue? I was surprised that Microsoft Support and the >Solver Help file did not mention that I need to have all formula >references on the same tab, and so am concerned there's other >limitations I am not aware of. > >I have tried changing all Solver Options too, but no help. > >I will try to simplify the involved data below: > >Solver changes these constants... >Cell A2 = 0.31 >Cell A3 = 0.25. >Cell A4 = 0.67 >Cell A5 = 0.52 > >to minimize the value of a target cell with the formula: >=SUM >(D41:E2000,I41:J2000,N41:O2000,S41:T2000,X41:Y2000,AC41:AD2000,AH41:AI2000,AM41:AN2000,AR41:AS2000,AW41:AX2000,BB41:BC2000) > >The columns summed in the target cell formula above have their own >formulas. An example would be: >=IF(AND(C41<A$2,C41<((A$6*B41)+A$9)),1,0), >where C41 and B41 are constants I am not changing in solver, A$2 is >one of the constants I am changing in solver, and A$6 and A$9 have >formulas that reference some of the constants I am changing but are >not directly inputted to the solver. > >Thus, solver changing any of A$2 through A$5, will change A$9 and/or A >$6, which in turn changes the column values that are summed in the >target cell formula, thus changing the target cell value. > >Thanks for any help, >K Regards, Tushar Mehta Microsoft MVP Excel 2000-present www.tushar-mehta.com Excel and PowerPoint tutorials and add-ins |