+ Reply to Thread
Results 1 to 8 of 8

Try to find optimal solution but did not work

  1. #1
    Registered User
    Join Date
    02-19-2024
    Location
    Indonesia
    MS-Off Ver
    2019
    Posts
    2

    Try to find optimal solution but did not work

    Hello! Right now i'm in the middle of a research related to inventory modelling and there is one paper that i use as main reference. In this paper, the optimal solution was obtained by using excel solver. In order for me to understans how to use excel solver, i try to find the optimal solution from this paper by using excel solver and if the result i obtained is the same as the result in the paper than the way i use excel solver is already correct. I'm pretty sure there is something wrong with the way i use excel solver but i dont know what i did wrong.

    I attached my excel file below.
    So basically i write all the numerical example and the mathematical modelling from the paper. I wrote the mathematical formula for each cost first and then the total cost. Cost 1 is for formula 1 like the picture i attach below.
    and the paper also attached below.

    Can you help me to explain what i'm supposed to do to obtained the optimal solution? And by the way, i'm really sorry if my question and explanation is not clear or confusing since i'm not that good in english.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by icebear22; 02-19-2024 at 01:27 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    10-17-2007
    Location
    Tasmania, Australia
    MS-Off Ver
    2019, 365(v2310)
    Posts
    366

    Re: Try to find optimal solution but did not work

    No one is going to goto bit.ly links that link to a google drive.

    Attach the files

  3. #3
    Registered User
    Join Date
    02-19-2024
    Location
    Indonesia
    MS-Off Ver
    2019
    Posts
    2

    Re: Try to find optimal solution but did not work

    I'm really sorry for that. This is my first time using this forum and it a little bit confusing so i thought it was okay to just attach the link. I fix it now, thank you!

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,829

    Re: Solver Can not find optimal solution

    When I open your file, I see a list of input variables (B2:B23). T is in B24, and n is in D24. There are formulas in B25:B34 that are returning errors.

    When I check the Solver model, it says it wants to set C23 to a maximum by changing B24. I notice that C23 is a blank cell. When I try to run the Solver model, I get the expected "Target cell must contain a formula" error message.

    I have not taken the time to explore the formulas in B25:C34, so I'm not sure which one, if any, ought to be the "target cell," nor have I tried to debug the errors in those formulas. If this were my project, I would identify the correct target cell. Then I would enter a reasonable value into B24 and make sure the target cell (and the other formulas) are calculating correctly. Maybe try entering a few additional values into B24 and make sure everything calculates correctly for a few different inputs. Then see if Solver can find a solution.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,570

    Re: Try to find optimal solution but did not work

    In the .pdf it appears that the equation is used to compute the three-echelon supply chain inventory system.
    My first suggestion is to search for "Excel three-echelon supply chain inventory system" to make sure that someone else has not already produced a way to accomplish this.
    If that doesn't work then it may help if you could give us at least one manually calculated expected result, say for "Cost 1" in cell B25, and explain how the result was obtained.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,829

    Re: Try to find optimal solution but did not work

    [note that this is a duplicate of #4]
    Last edited by MrShorty; 02-20-2024 at 01:21 PM.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,063

    Re: Try to find optimal solution but did not work

    icebear. DO NOT duplicate threads. Only one thread per topic is permitted. I have merged the two threads.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,829

    Re: Try to find optimal solution but did not work

    It also appears that the sample file in this thread is different from the file in the other thread. In this file, there are two tabs. One looks like the same one in the other thread. The "Sheet1" tab in this file looks more complete. I still see errors in B25:D34 that need to be resolved. The few errors that I've caught look like "implied multiplication" type of errors where, for example, you enter ...B16(1-B17)... without the required multiplication operator.

    If I "fix" those kinds of errors, I get a value in D34, then Solver seems to be able to find a maximum. At this point, it looks like you just need to go through your formulas and fix the various syntax errors and carefully evaluate and debug them to make sure they are working correctly. I expect that, once you get the formulas debugged, you will have little difficulty with the Solver model.

+ 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. Finding the most optimal solution - F1 Fantasy League
    By Excel_Beg!nner in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-18-2022, 11:46 PM
  2. [SOLVED] Solver not finding optimal solution
    By RollingDice in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-07-2022, 10:32 AM
  3. [SOLVED] Solver not returning optimal solution
    By RollingDice in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-25-2022, 01:02 PM
  4. [SOLVED] Excel Solver- Optimal Salary Lineup- Solver does not find optimal solution
    By Sleven369 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-07-2021, 04:04 PM
  5. All combinations + search for optimal solution
    By Arthfael in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-08-2015, 12:03 AM
  6. Replies: 11
    Last Post: 10-30-2013, 02:11 PM
  7. solver - not always optimal solution ?
    By przemke in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-07-2009, 03:38 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