+ Reply to Thread
Results 1 to 4 of 4

Solver problem with ROUND function

  1. #1
    Registered User
    Join Date
    02-11-2015
    Location
    Santa Rosa, CA
    MS-Off Ver
    2019
    Posts
    39

    Solver problem with ROUND function

    I am using solver on a chain of cells. If one of the cells contains a ROUND function, such as =ROUND((C16-D16)*$D$12+D16,0), solver returns an incorrect answer. If I remove the ROUND function, such as (C16-D16)*$D$12+D16, solver returns the correct answer. Any ideas on why solver does not like the ROUND function?

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Solver problem with ROUND function

    Because it makes the function discontinuous. Solver works by making small changes in variables watching the result to determine what to do next, and ROUND (and other functions) break that paradigm.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Solver problem with ROUND function

    I tested & found both formula get same result. (Tested in "Excel2007")
    Refer attachment.
    Attached Images Attached Images


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  4. #4
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Solver problem with ROUND function

    Quote Originally Posted by Jim_Robison View Post
    Any ideas on why solver does not like the ROUND function?
    As shg explained, the default Solver non-linear method (GRG) expects "continuous" results. That is, small changes in inputs results in changes in outputs in a convergent manner.

    Functions like ROUND and even IF and other conditional and lookup functions result in "discrete" results. That is, changes in inputs don't always alter outputs, or the changes are not convergent..

    I believe that is also true of the Solver linear method (called LP Simplex in Excel 2010 and later); that is, it expects "continuous" results.

    However, Excel 2010 introduced a Solver method called Evolutionary. It is designed for "non-smooth" problems. I believe that means "discontinuous" results.

    I no longer have access to that version. But as I recall, it is essentially a random method.

    If you have Excel 2010 or later, click Options in Solver and see if the "non-smooth" method works for you.

    -----

    Quote Originally Posted by avk View Post
    I tested & found both formula get same result. (Tested in "Excel2007")
    Sometimes, we get lucky. After all, it depends on whether functions that return "discrete" values change the output in a manner that seems to be convergent, at least as far as Solver sees it.
    Last edited by joeu2004; 04-07-2017 at 10:46 AM. Reason: minor typos

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Problem Inserting Round function into an IF function
    By Ash87 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-11-2013, 05:37 PM
  2. Problem solver-function
    By Lv27 in forum Excel General
    Replies: 2
    Last Post: 09-18-2012, 08:57 AM
  3. Problem using solver with COUNTIF function
    By hellocanuhearme in forum Excel General
    Replies: 1
    Last Post: 04-17-2012, 08:43 AM
  4. Round function problem
    By SinGin in forum Excel General
    Replies: 1
    Last Post: 01-25-2011, 10:17 AM
  5. SOLVER Function Problem
    By rwakeham in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-12-2007, 09:36 AM
  6. [SOLVED] Solver problem where 'IF Function' is bad
    By David Adamson in forum Excel General
    Replies: 5
    Last Post: 09-23-2005, 02:05 AM
  7. Replies: 2
    Last Post: 07-08-2005, 09:05 AM
  8. Problem with the round function
    By Patrick in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-31-2005, 01:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1