+ Reply to Thread
Results 1 to 2 of 2

Problem with Excel Solver not able to find a solution while there should be one

  1. #1
    Registered User
    Join Date
    12-04-2013
    Location
    Amsterdam
    MS-Off Ver
    Excel 2007
    Posts
    1

    Problem with Excel Solver not able to find a solution while there should be one

    Cutting problem COUNTIF function.xlsxCutting problem ROUNDUP function.xlsxCutting problem COUNTIF function.xlsxCutting problem ROUNDUP function.xlsxHello all,

    Currently we are bussy with a project in which we need to find the optimal cutting solution.
    We are working for a fictional company that produces steel rolls. Each of the rolls need to subject to certain constraints that can differ by each order.

    In this particular example the fictional company has 5 rolls that weight respectively: 6, 15, 22, 26, 28 tons. These rolls need to be cut up to rolls that weigh between 12 to 15 tons. This means that some rolls need to be welded together. Each welded roll can only have one weld joint.

    We have succeeded in formulating the constraints that seeks the optimal solution in which the begin rolls can be cut between the bounderies of 12 to 15 tons. But are unable to formulate a constraint for the welding joint (which can only be one in this case).

    We have tried to use the function COUNTIF but we found out that the all the IF function are not compatible with the "excel solver". We tried using a different approach which was deviding each individual "changable variable cell" by its own value + 0.01 and rounding the answer up (since you cannot divide by zero, and you only can weld 2 plates together so each column of the changable variable cells will have a zero in them). This approach did also not work.

    Our question there for is how to approach the welding problem?

    IF you have any questions regarding this problem then please let me know!

    Kind regards & Thanks in advance,

    Mauzzzzzzz,

    Attachment1 with the COUNTIF function:


    Attachment2 with the ROUNDUP function:

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Problem with Excel Solver not able to find a solution while there should be one

    COUNTIF but we found out that the all the IF function are not compatible with the "excel solver".
    Yes this is because solver can't handle discontinous fuctions.

    The most common discontinuous function in Excel is the IF function where the conditional test is dependent on the decision variables. Other common discontinuous functions are CHOOSE, the LOOKUP functions, and COUNT. Common non-smooth functions in Excel are ABS, MIN and MAX, INT and ROUND, and CEILING and FLOOR. Functions such as SUMIF and the database functions are discontinuous if the criterion or conditional argument depends on the decision variables.
    If you have access to Excel 2010 you could try the "Evolutionary Solver" as this is supposed to handle discontinous function.

    I've had a look at you model but I'm not sure I understands your problem propperly still I've set up a small model of how I would model a problem like this.

    Alf
    Attached Files Attached Files

+ 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. Replies: 11
    Last Post: 10-30-2013, 02:11 PM
  2. Replies: 2
    Last Post: 09-04-2013, 08:36 PM
  3. Excel Solver giving wrong optimal solution
    By gtg430i in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-11-2008, 02:29 PM
  4. Why is excel solver saying that there is not a feasible solution?
    By jt13 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-17-2006, 01:30 PM
  5. [SOLVED] Solver does not find correct solution???
    By experiment626 in forum Excel General
    Replies: 5
    Last Post: 08-18-2005, 07:05 PM

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