|
Solver returns non binary answer in binary constrained cells
If I constrain a reference cell to binary (i.e. =binary), solver returns
fractional answers. I even added integer constraints to the cells and still got fractional answers. |
Thanks for the feedback. Solver does say it solved the problem and met my
constraints even though it did not. The other issue you brought up has also given me problems. If I want to perform an IF, THEN check and I want a number equal to 1, the .99999999999 will still turn up false for the IF, THEN logic arguement. Any help with either would be appreciated. "Tushar Mehta" wrote: In article , =?Utf- 8?B?TmF2eSBTdHVkZW50?= <Navy says... If I constrain a reference cell to binary (i.e. =binary), solver returns fractional answers. I even added integer constraints to the cells and still got fractional answers. Did it solve the problem? Or did it pop up with an error about how it cannot find a solution? Solver uses something called the branch-and-bound algorithm. The consequence of this approach is that only when it reaches a solution are the binary/integer constraints honored. So, if it doesn't reach a solution, the values can be non-integer. Even when it reaches a solution, what is considered an integer value is often within the realm of "close enough." In other words, it might leave a result at 0.9999999 instead of exactly 1. I believe -- but am not sure -- that this is controlled by the 'Precision' value in the Solver options dialog box. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions |
=IF(ABS(solution-1)<epsilon,...)
Jerry Navy Student wrote: Thanks for the feedback. Solver does say it solved the problem and met my constraints even though it did not. The other issue you brought up has also given me problems. If I want to perform an IF, THEN check and I want a number equal to 1, the .99999999999 will still turn up false for the IF, THEN logic arguement. Any help with either would be appreciated. "Tushar Mehta" wrote: In article , =?Utf- 8?B?TmF2eSBTdHVkZW50?= <Navy says... If I constrain a reference cell to binary (i.e. =binary), solver returns fractional answers. I even added integer constraints to the cells and still got fractional answers. Did it solve the problem? Or did it pop up with an error about how it cannot find a solution? Solver uses something called the branch-and-bound algorithm. The consequence of this approach is that only when it reaches a solution are the binary/integer constraints honored. So, if it doesn't reach a solution, the values can be non-integer. Even when it reaches a solution, what is considered an integer value is often within the realm of "close enough." In other words, it might leave a result at 0.9999999 instead of exactly 1. I believe -- but am not sure -- that this is controlled by the 'Precision' value in the Solver options dialog box. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions |
Jerry, thanks for trying to help but I don't understand your reply. Also I'm
not sure that I can use operations such as ABS in cells that are being changed by SOLVER. "Jerry W. Lewis" wrote: =IF(ABS(solution-1)<epsilon,...) Jerry Navy Student wrote: Thanks for the feedback. Solver does say it solved the problem and met my constraints even though it did not. The other issue you brought up has also given me problems. If I want to perform an IF, THEN check and I want a number equal to 1, the .99999999999 will still turn up false for the IF, THEN logic arguement. Any help with either would be appreciated. "Tushar Mehta" wrote: In article , =?Utf- 8?B?TmF2eSBTdHVkZW50?= <Navy says... If I constrain a reference cell to binary (i.e. =binary), solver returns fractional answers. I even added integer constraints to the cells and still got fractional answers. Did it solve the problem? Or did it pop up with an error about how it cannot find a solution? Solver uses something called the branch-and-bound algorithm. The consequence of this approach is that only when it reaches a solution are the binary/integer constraints honored. So, if it doesn't reach a solution, the values can be non-integer. Even when it reaches a solution, what is considered an integer value is often within the realm of "close enough." In other words, it might leave a result at 0.9999999 instead of exactly 1. I believe -- but am not sure -- that this is controlled by the 'Precision' value in the Solver options dialog box. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions |
I understood you to say that you had an IF formula that evaluated
whether the solver anser was equal to one or not. Instead of your current formula of =IF(solution=1,...) change that formula to =IF(ABS(solution-1)<epsilon,...) Replace "solution" with the unstated cell reference that you are currently using. Replace "epsilon" with a suitably small value, such as 1E-10 Jerry Navy Student wrote: Jerry, thanks for trying to help but I don't understand your reply. Also I'm not sure that I can use operations such as ABS in cells that are being changed by SOLVER. "Jerry W. Lewis" wrote: =IF(ABS(solution-1)<epsilon,...) Jerry Navy Student wrote: Thanks for the feedback. Solver does say it solved the problem and met my constraints even though it did not. The other issue you brought up has also given me problems. If I want to perform an IF, THEN check and I want a number equal to 1, the .99999999999 will still turn up false for the IF, THEN logic arguement. Any help with either would be appreciated. "Tushar Mehta" wrote: In article , =?Utf- 8?B?TmF2eSBTdHVkZW50?= <Navy says... If I constrain a reference cell to binary (i.e. =binary), solver returns fractional answers. I even added integer constraints to the cells and still got fractional answers. Did it solve the problem? Or did it pop up with an error about how it cannot find a solution? Solver uses something called the branch-and-bound algorithm. The consequence of this approach is that only when it reaches a solution are the binary/integer constraints honored. So, if it doesn't reach a solution, the values can be non-integer. Even when it reaches a solution, what is considered an integer value is often within the realm of "close enough." In other words, it might leave a result at 0.9999999 instead of exactly 1. I believe -- but am not sure -- that this is controlled by the 'Precision' value in the Solver options dialog box. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions |
Jerry, Thanks much, that helps me alot to fix my IF statements. I still have
the issue with SOLVER giving answers that are nowhere close to binary. I get answers such as 0.147 and 0.651 on cells that are constrained to be binary. I have looked hard at the Solver Options, increased time, interations and precision without success...any thoughts? "Jerry W. Lewis" wrote: I understood you to say that you had an IF formula that evaluated whether the solver anser was equal to one or not. Instead of your current formula of =IF(solution=1,...) change that formula to =IF(ABS(solution-1)<epsilon,...) Replace "solution" with the unstated cell reference that you are currently using. Replace "epsilon" with a suitably small value, such as 1E-10 Jerry Navy Student wrote: Jerry, thanks for trying to help but I don't understand your reply. Also I'm not sure that I can use operations such as ABS in cells that are being changed by SOLVER. "Jerry W. Lewis" wrote: =IF(ABS(solution-1)<epsilon,...) Jerry Navy Student wrote: Thanks for the feedback. Solver does say it solved the problem and met my constraints even though it did not. The other issue you brought up has also given me problems. If I want to perform an IF, THEN check and I want a number equal to 1, the .99999999999 will still turn up false for the IF, THEN logic arguement. Any help with either would be appreciated. "Tushar Mehta" wrote: In article , =?Utf- 8?B?TmF2eSBTdHVkZW50?= <Navy says... If I constrain a reference cell to binary (i.e. =binary), solver returns fractional answers. I even added integer constraints to the cells and still got fractional answers. Did it solve the problem? Or did it pop up with an error about how it cannot find a solution? Solver uses something called the branch-and-bound algorithm. The consequence of this approach is that only when it reaches a solution are the binary/integer constraints honored. So, if it doesn't reach a solution, the values can be non-integer. Even when it reaches a solution, what is considered an integer value is often within the realm of "close enough." In other words, it might leave a result at 0.9999999 instead of exactly 1. I believe -- but am not sure -- that this is controlled by the 'Precision' value in the Solver options dialog box. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions |
| All times are GMT +1. The time now is 01:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com