Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Navy Student
 
Posts: n/a
Default 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.
  #4   Report Post  
Jerry W. Lewis
 
Posts: n/a
Default

=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


  #5   Report Post  
Navy Student
 
Posts: n/a
Default

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





  #6   Report Post  
Jerry W. Lewis
 
Posts: n/a
Default

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


  #7   Report Post  
Navy Student
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Solver Constraints Rick Excel Discussion (Misc queries) 15 March 9th 05 04:17 PM
Solver "returns no value". Please help Marlon Brown Excel Worksheet Functions 2 February 6th 05 06:25 PM
How to solve a formula so that it returns actual answer if negati. Wayne Excel Discussion (Misc queries) 2 December 22nd 04 08:46 PM
Logic statement returns wrong answer. Tony Excel Worksheet Functions 2 December 2nd 04 06:07 AM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 01:59 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"