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.
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.
In article <[email protected]>, =?Utf-
8?B?TmF2eSBTdHVkZW50?= <Navy [email protected]> 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
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 <[email protected]>, =?Utf-
> 8?B?TmF2eSBTdHVkZW50?= <Navy [email protected]> 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 <[email protected]>, =?Utf-
>>8?B?TmF2eSBTdHVkZW50?= <Navy [email protected]> 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 <[email protected]>, =?Utf-
> >>8?B?TmF2eSBTdHVkZW50?= <Navy [email protected]> 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 <[email protected]>, =?Utf-
>>>>8?B?TmF2eSBTdHVkZW50?= <Navy [email protected]> 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 <[email protected]>, =?Utf-
> >>>>8?B?TmF2eSBTdHVkZW50?= <Navy [email protected]> 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
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks