+ Reply to Thread
Results 1 to 2 of 2

SOLVER - optimization of equation coefficients

  1. #1
    Registered User
    Join Date
    09-22-2020
    Location
    Wroclaw, Poland
    MS-Off Ver
    2013
    Posts
    1

    SOLVER - optimization of equation coefficients

    Hi !

    I'd be super glad if someone could assist me with the following Solver task. In the table attached in the 1st column there is contract value, in the 2nd - present commission, and in the 3rd we have an equasion that expresses new commission that will soon replace the present one. Next to the data there is a table with coefficients for the equasion.
    What's wring with the present commissions? They're threshold commissions which means that they are, let's say, unjust, eg. the commission is 10 if contract value=200 but if the value increases by 1, to 201, the commission jumps to 20 (sic).

    Now, the task is to find optimal coefficients. Objectives? The most important one is this:
    - the total of new commissions must not be less than that total of present commissions (the earnings must remain the same).

    I tried to do it like that:
    - goal/aim cell is the total of new commissions
    - the target should equal the abovementioned total (i typed it in)
    - the changed cells are my 3 coefficients
    - I was adding constraints but it didn't change a thing

    When I tried to solve it, the Solver said "no no, I cannot do it for you, pal". But I have a feeling that essentially, I'm not looking at it from the right angle.
    I'm attaching a clean file with pure data in it.

    Could you give me a hint?
    Attached Files Attached Files

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

    Re: SOLVER - optimization of equation coefficients

    I would be curious exactly what you tried. The first thing I notice is that column C contains a ROUND() function. Rounding functions take nice, smooth functions (that Solver likes to work with) and turns them into discrete step functions (that Solver does not like). If every attempt at solving this has included the ROUND() function in column C, I would remove the ROUND() function -- let solver find the coefficients for the equation, then evaluate the inevitable rounding errors after you have solved the unrounded version of the problem. When I did this it looked like:
    1) Edit C2 to remove the ROUND() function =-He/(A2-Sxe)+Le*A2+Sye. Copy/paste/fill to the bottom.
    2) Call Solver and tell it to:
    2a) Set C294
    2b) To a value of 7602.42
    2c) By changing F4:F6
    2d) Subject to the constraint that C2>=0.

    Solver readily found a solution. I then added a column =ROUND(C2,2) and summed it up to evaluate the effect of rounding. C294 is 7602.42 as dictated by the Solver run. The sum of the rounded values is 7602.36. From there, you would need to decide what, if anything to do about the rounding differences/errors.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ 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. Estimating multiple coefficients of an equation
    By Simplifier12 in forum Excel General
    Replies: 13
    Last Post: 10-23-2018, 06:57 AM
  2. Determining coefficients of an equation using your experimental data?
    By Iman01 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-02-2015, 01:33 PM
  3. [SOLVED] Copy coefficients of an equation produced by a graph into cells
    By mogoldberg in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2013, 06:38 PM
  4. how to constrain coefficients in a multiple regression using solver
    By psheeha1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-27-2012, 11:02 PM
  5. [SOLVED] Finding the Coefficients of a Quadratic Equation using VBA
    By kyleg222 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-26-2012, 10:38 AM
  6. Trend Line Equation Coefficients
    By Jake in forum Excel General
    Replies: 4
    Last Post: 04-17-2006, 03:50 PM
  7. [SOLVED] VBA coefficients of an equation
    By Ali Baba in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-21-2005, 12:05 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