Home 
Search 
Today's Posts 
#1




Solver not working
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,A W41: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 
#2




Solver not working
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" wrote in message ... 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,A W41: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 
#3




Solver not working
On Dec 1, 9:24*pm, "Bernard Liengme"
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" wrote in message ... 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,A W41: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 
#4




Solver not working
Check will www.solver.com. Maybe one of the premium version will work
best wishes "Kerry" wrote in message ... On Dec 1, 9:24 pm, "Bernard Liengme" 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" wrote in message ... 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,A W41: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 
#5




Solver not working
I haven't read your post in detail but a long time ago I shared
several tips on how to convert various nonlinear criteria into linear ones. See my posts in http://groups.google.com/group/micro...f16683d9c0b0d5 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 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:Y200 0,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 2000present www.tusharmehta.com Excel and PowerPoint tutorials and addins 
#6




Solver not working
On Dec 2, 12:09*pm, Tushar Mehta <ngunderscoreposterattushar
hyphenmehta.seeohem wrote: I haven't read your post in detail but a long time ago I shared several tips on how to convert various nonlinear criteria into linear ones. See my posts inhttp://groups.google.com/group/microsoft.public.excel.programming/bro... 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 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:Y200 0,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 2000presentwww.tusharmehta.com Excel and PowerPoint tutorials and addins Hi, I found where you address the IF statement, but I'm having trouble following. I think this is on the right path for me, so I'd appreciate any patience and help to clarify. I've posted your previous post below with questions at the end of each sentence: "First, the IF statement. Suppose that a firm has a choice of 2 plants where it can produce a product. If it uses a particular plant to produce any amount of the product, it incurs a fixed cost of say $50,000." Do I understand this as choose the plant that creates more product for the fixed $50,000 price? "This has the nature of an IF statement of the type [IF x0 then K else 0], where K is a constant." I don't get what x or K represent. Does x = amount of product and K = $50,000? "One can replace the IF with linear equations by introducing a binary variable, b, and a large constant, say, M. Now, the IF statement becomes K*b x <= M*b b = 0/1 (b is binary) x = 0 How does it work? If x is anything other than 0, the x <= M*b will be satisfied only if b is 1. If b is 1, the K*b will evaluate to K! Also, since M is a very large number, once b is 1, x <= M*b will always be true no matter how large x becomes". Does x represent essentially the binary threshold (i.e. less than x then with this plant, more than x go with the other plant). If so, can it be a nonzero number?  Also I don't get how the binary is applied in Excel. Thanks! K 
#7




Solver not working
On Dec 2, 1:52*pm, Kerry wrote:
On Dec 2, 12:09*pm, Tushar Mehta <ngunderscoreposterattushar hyphenmehta.seeohem wrote: I haven't read your post in detail but a long time ago I shared several tips on how to convert various nonlinear criteria into linear ones. See my posts inhttp://groups.google.com/group/microsoft.public.excel.programming/bro... 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 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:Y200 0,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 2000presentwww.tusharmehta.com Excel and PowerPoint tutorials and addins Hi, I found where you address the IF statement, but I'm having trouble following. I think this is on the right path for me, so I'd appreciate any patience and help to clarify. I've posted your previous post below with questions at the end of each sentence: "First, the IF statement. *Suppose that a firm has a choice of 2 plants where it can produce a product. *If it uses a particular plant to produce any amount of the product, it incurs a fixed cost of say $50,000." Do I understand this as choose the plant that creates more product for the fixed $50,000 price? "This has the nature of an IF statement of the type [IF x0 then K else 0], where K is a constant." I don't get what x or K represent. Does x = amount of product and K = $50,000? "One can replace the IF with linear equations by introducing a binary variable, b, and a large constant, say, M. *Now, the IF statement becomes * * K*b * * x <= M*b * * b = 0/1 (b is binary) * * x = 0 How does it work? *If x is anything other than 0, the x <= M*b will be satisfied only if b is 1. *If b is 1, the K*b will evaluate to K! Also, since M is a very large number, once b is 1, x <= M*b will always be true no matter how large x becomes". Does x represent essentially the binary threshold (i.e. less than x then with this plant, more than x go with the other plant). If so, can it be a nonzero number?  Also I don't get how the binary is applied in Excel. Thanks! K Can anyone help clarify this please? I've seen a similar solution elsewhere but I can't make out the explanations and thus how to fit to my data. What is a binary variable in excel, how do I incorporate it and wouldn't it also cause the function gaps or sudden jumps that Solver has issues with? To simplify my example above I have the below example, though the real Excel equaitions are more complicated: I have a column with: C1 =IF(A1<B1,1,0) C2 =IF(A2<B1,1,0) ..... C1000 =IF(A1000<B1,1,0) Then, D1 = sum(C1:C1000) Solver is asked to reduce D1 (i.e. target cell) by changing B1. I found a method using absolute values in the C column equation, e.g. C1 =((A1B1)+ABS(A1B1))*(1/(2*(A1B1))). This creates values of 0 or 1, but I think this will have the same issue since absolute values can mess up functions too. I'm currently trying to work it in, which is tough because the real C columns are IFAND arguments. Thanks again, K 
#8




Solver not working
On Dec 6, 10:08*pm, Kerry wrote:
On Dec 2, 1:52*pm, Kerry wrote: On Dec 2, 12:09*pm, Tushar Mehta <ngunderscoreposterattushar hyphenmehta.seeohem wrote: I haven't read your post in detail but a long time ago I shared several tips on how to convert various nonlinear criteria into linear ones. See my posts inhttp://groups.google.com/group/microsoft.public.excel.programming/bro... 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 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:Y200 0,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 2000presentwww.tusharmehta.com Excel and PowerPoint tutorials and addins Hi, I found where you address the IF statement, but I'm having trouble following. I think this is on the right path for me, so I'd appreciate any patience and help to clarify. I've posted your previous post below with questions at the end of each sentence: "First, the IF statement. *Suppose that a firm has a choice of 2 plants where it can produce a product. *If it uses a particular plant to produce any amount of the product, it incurs a fixed cost of say $50,000." Do I understand this as choose the plant that creates more product for the fixed $50,000 price? "This has the nature of an IF statement of the type [IF x0 then K else 0], where K is a constant." I don't get what x or K represent. Does x = amount of product and K = $50,000? "One can replace the IF with linear equations by introducing a binary variable, b, and a large constant, say, M. *Now, the IF statement becomes * * K*b * * x <= M*b * * b = 0/1 (b is binary) * * x = 0 How does it work? *If x is anything other than 0, the x <= M*b will be satisfied only if b is 1. *If b is 1, the K*b will evaluate to K! Also, since M is a very large number, once b is 1, x <= M*b will always be true no matter how large x becomes". Does x represent essentially the binary threshold (i.e. less than x then with this plant, more than x go with the other plant). If so, can it be a nonzero number?  Also I don't get how the binary is applied in Excel. Thanks! K Can anyone help clarify this please? I've seen a similar solution elsewhere but I can't make out the explanations and thus how to fit to my data. What is a binary variable in excel, how do I incorporate it and wouldn't it also cause the function gaps or sudden jumps that Solver has issues with? To simplify my example above I have the below example, though the real Excel equaitions are more complicated: I have a column with: C1 =IF(A1<B1,1,0) C2 =IF(A2<B1,1,0) .... C1000 =IF(A1000<B1,1,0) Then, D1 = sum(C1:C1000) Solver is asked to reduce D1 (i.e. target cell) by changing B1. I found a method using absolute values in the C column equation, e.g. C1 =((A1B1)+ABS(A1B1))*(1/(2*(A1B1))). This creates values of 0 or 1, but I think this will have the same issue since absolute values can mess up functions too. I'm currently trying to work it in, which is tough because the real C columns are IFAND arguments. Thanks again, K Actually, I think I may be able to just remove the ABS and attempts to make the values 0 and 1, and instead just try to find the max value in solver. This will maximize the # of positive values, which are all the ones that would be 0s and not 1s in my case. Then, postsolver, I can simply count the number of positive values. I'll post again the results! 
#9




Solver not working
On Dec 6, 10:08*pm, Kerry wrote:
On Dec 2, 1:52*pm, Kerry wrote: On Dec 2, 12:09*pm, Tushar Mehta <ngunderscoreposterattushar hyphenmehta.seeohem wrote: I haven't read your post in detail but a long time ago I shared several tips on how to convert various nonlinear criteria into linear ones. See my posts inhttp://groups.google.com/group/microsoft.public.excel.programming/bro... 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 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:Y200 0,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 2000presentwww.tusharmehta.com Excel and PowerPoint tutorials and addins Hi, I found where you address the IF statement, but I'm having trouble following. I think this is on the right path for me, so I'd appreciate any patience and help to clarify. I've posted your previous post below with questions at the end of each sentence: "First, the IF statement. *Suppose that a firm has a choice of 2 plants where it can produce a product. *If it uses a particular plant to produce any amount of the product, it incurs a fixed cost of say $50,000." Do I understand this as choose the plant that creates more product for the fixed $50,000 price? "This has the nature of an IF statement of the type [IF x0 then K else 0], where K is a constant." I don't get what x or K represent. Does x = amount of product and K = $50,000? "One can replace the IF with linear equations by introducing a binary variable, b, and a large constant, say, M. *Now, the IF statement becomes * * K*b * * x <= M*b * * b = 0/1 (b is binary) * * x = 0 How does it work? *If x is anything other than 0, the x <= M*b will be satisfied only if b is 1. *If b is 1, the K*b will evaluate to K! Also, since M is a very large number, once b is 1, x <= M*b will always be true no matter how large x becomes". Does x represent essentially the binary threshold (i.e. less than x then with this plant, more than x go with the other plant). If so, can it be a nonzero number?  Also I don't get how the binary is applied in Excel. Thanks! K Can anyone help clarify this please? I've seen a similar solution elsewhere but I can't make out the explanations and thus how to fit to my data. What is a binary variable in excel, how do I incorporate it and wouldn't it also cause the function gaps or sudden jumps that Solver has issues with? To simplify my example above I have the below example, though the real Excel equaitions are more complicated: I have a column with: C1 =IF(A1<B1,1,0) C2 =IF(A2<B1,1,0) .... C1000 =IF(A1000<B1,1,0) Then, D1 = sum(C1:C1000) Solver is asked to reduce D1 (i.e. target cell) by changing B1. I found a method using absolute values in the C column equation, e.g. C1 =((A1B1)+ABS(A1B1))*(1/(2*(A1B1))). This creates values of 0 or 1, but I think this will have the same issue since absolute values can mess up functions too. I'm currently trying to work it in, which is tough because the real C columns are IFAND arguments. Thanks again, K I got using ABS to work in creating 0s and 1s and thus removed the IF statement, but it seems to fail for the same reason as using IF in SOLVER. I was thinking I could remove ABS from the above equation C1 =((A1 B1)+ABS(A1B1))*(1/(2*(A1B1))) and turn into: C1 =(A1B1). This creates negative and positive number, where negatives would have = 0 in the ABS equation and 1 for the positives. Then I'd sum the values in the target cell and ask Solver to maximize the values. Thus it would try to push as many values above 1 as possible. The problem is twofold: 1. There will be a dependency on the magnitude of values, which is incorrect in my case (all values should be equally important in my case) 2. Because my real equation which I've simplified above is really not an IF but instead IFAND statement, I need something that considers only 1 of the criteria above 0 to be as good ("optimized") as ALL of the criteria equaling 1) It seems I need a way to get my neg and pos values to equal 0 and 1 respectively (or vice versa) PREsolver, without using ABS or any other Solver stopping functions K 
#10




Solver not working
On Dec 7, 12:02*am, Kerry wrote:
On Dec 6, 10:08*pm, Kerry wrote: On Dec 2, 1:52*pm, Kerry wrote: On Dec 2, 12:09*pm, Tushar Mehta <ngunderscoreposterattushar hyphenmehta.seeohem wrote: I haven't read your post in detail but a long time ago I shared several tips on how to convert various nonlinear criteria into linear ones. See my posts inhttp://groups.google.com/group/microsoft.public.excel.programming/bro... 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 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:Y200 0,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 2000presentwww.tusharmehta.com Excel and PowerPoint tutorials and addins Hi, I found where you address the IF statement, but I'm having trouble following. I think this is on the right path for me, so I'd appreciate any patience and help to clarify. I've posted your previous post below with questions at the end of each sentence: "First, the IF statement. *Suppose that a firm has a choice of 2 plants where it can produce a product. *If it uses a particular plant to produce any amount of the product, it incurs a fixed cost of say $50,000." Do I understand this as choose the plant that creates more product for the fixed $50,000 price? "This has the nature of an IF statement of the type [IF x0 then K else 0], where K is a constant." I don't get what x or K represent. Does x = amount of product and K = $50,000? "One can replace the IF with linear equations by introducing a binary variable, b, and a large constant, say, M. *Now, the IF statement becomes * * K*b * * x <= M*b * * b = 0/1 (b is binary) * * x = 0 How does it work? *If x is anything other than 0, the x <= M*b will be satisfied only if b is 1. *If b is 1, the K*b will evaluate to K! Also, since M is a very large number, once b is 1, x <= M*b will always be true no matter how large x becomes". Does x represent essentially the binary threshold (i.e. less than x then with this plant, more than x go with the other plant). If so, can it be a nonzero number?  Also I don't get how the binary is applied in Excel. Thanks! K Can anyone help clarify this please? I've seen a similar solution elsewhere but I can't make out the explanations and thus how to fit to my data. What is a binary variable in excel, how do I incorporate it and wouldn't it also cause the function gaps or sudden jumps that Solver has issues with? To simplify my example above I have the below example, though the real Excel equaitions are more complicated: I have a column with: C1 =IF(A1<B1,1,0) C2 =IF(A2<B1,1,0) .... C1000 =IF(A1000<B1,1,0) Then, D1 = sum(C1:C1000) Solver is asked to reduce D1 (i.e. target cell) by changing B1. I found a method using absolute values in the C column equation, e.g. C1 =((A1B1)+ABS(A1B1))*(1/(2*(A1B1))). This creates values of 0 or 1, but I think this will have the same issue since absolute values can mess up functions too. I'm currently trying to work it in, which is tough because the real C columns are IFAND arguments. Thanks again, K I got using ABS to work in creating 0s and 1s and thus removed the IF statement, but it seems to fail for the same reason as using IF in SOLVER. I was thinking I could remove ABS from the above equation C1 =((A1 B1)+ABS(A1B1))*(1/(2*(A1B1))) and turn into: *C1 =(A1B1). This creates negative and positive number, where negatives would have = 0 in the ABS equation and 1 for the positives. Then I'd sum the values in the target cell and ask Solver to maximize the values. Thus it would try to push as many values above 1 as possible. The problem is twofold: 1. There will be a dependency on the magnitude of values, which is incorrect in my case (all values should be equally important in my case) 2. Because my real equation which I've simplified above is really not an IF but instead IFAND statement, I need something that considers only 1 of the criteria above 0 to be as good ("optimized") as ALL of the criteria equaling 1) It seems I need a way to get my neg and pos values to equal 0 and 1 respectively (or vice versa) PREsolver, without using ABS or any other Solver stopping functions K By the way, I just tried using SQRT of the squared values instead of ABS and SOLVER doesn't work with it. I'm not surprised since I guess Sqrt can cause gaps or jumps just like ABS. Now I'm getting worried. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Solver or Other?  Excel Worksheet Functions  
Will Solver do the job?  Excel Discussion (Misc queries)  
Calculate working days but change working week  Excel Discussion (Misc queries)  
Interesting Solver problem (Solver encounters an error)  Excel Discussion (Misc queries)  
solver  Excel Worksheet Functions 