+ Reply to Thread
Results 1 to 6 of 6

Help with Solver

  1. #1
    Registered User
    Join Date
    04-20-2015
    Location
    oldsmar
    MS-Off Ver
    2010
    Posts
    4

    Help with Solver

    Hi All - have a relatively beginner question here having to do with the solver addin.

    Problem: I have a model in excel that projects total points scored. To get to this projected number, I have a set of weights that are input-able, and drive how much of a teams historical stats get weight in the calculation. This is shown in image 1.

    After the total points are projected, I am back testing the total versus actual results and comparing how my projection did with the actual result. Essentially, after the total is projected, there is a long chain of other cells that change, and then ultimately I have a Win % to see how I did with my projections. I am trying to use solver to find the optimal weightings of each stat to maximize the Win %. Image 2 has the settings I am using in solver.

    When I click solve, Solver runs in like 1 second and come back with weights of 1 and 1 (ie 100% and 100%) for each stat. I know that this is not optimal based on manually inputting different percents and seeing a higher Win %.

    Am I doing something wrong? Is the fact that after the input cells are input to, theres a long chain of other cells that need to update a problem?

    Thanks in advance for your help!
    Attached Images Attached Images

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Help with Solver

    No images please. They are not very useful and none of us want to spend time recreating what you already have.

    If you want help then you have the workbook so upload it.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

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

    Re: Help with Solver

    I don't see anything inherently wrong in your picture or description. Long calculation chains between decision variables (R532:S533) and Objective function (U5) are not, by themselves, a problem for Solver.

    Without seeing the the spreadsheet, I would guess that something about the calculations that go into the objective function creates some sub-optimal behavior in U5. Step functions (where small changes in decision variables result in no change at all in the objective) like COUNT() and IF() and VLOOKUP() tend to create problems for Solver. Objective functions with multiple maxima can cause problems -- especially when the goal is to find the global maximum and not a local maximum.

    If I were to debug this, the first thing I would do would be to make a small change to one or more inputs, and see how U5 changes. Work on understanding for myself how U5 changes when R532 changes, then I will be in a better position to figure out what changes need to be made in the Solver model or in the objective function.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    04-20-2015
    Location
    oldsmar
    MS-Off Ver
    2010
    Posts
    4

    Re: Help with Solver

    Thanks for the responses, everyone. I have uploaded a simple excel workbook that essentially shows what I am trying to do. I also attached an image of the parameters I am using. Essentially, I want the yellow cell to be as high as it can be by playing with the different weights.

    Thanks in advance for your help!
    Attached Images Attached Images
    Attached Files Attached Files

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

    Re: Help with Solver

    COUNT() and COUNTIF() are definitely step functions, and, as I noted, step functions tend to cause problems for Solver's algorithms. If you are going to solve this problem using Solver, I think you will need to use an Evolutionary engine and hope it can find solutions.

    If you are not required to use Solver, I might suggest that something like a Data Table can be more effective at finding max/mins for problems like this. https://www.excel-easy.com/examples/data-tables.html Steps I used:

    1) Your two constraints are simply about forcing the two "sets" of decision variables to sum to 1. I replaced the hard coded values in R4 and S4 with the formulas =1-R3 (copy into S4). Now the only changing values are R3 and S3.
    2) Set up my 2D data table
    2a) In T21, enter =N34
    2b) In T22:T30, enter numbers from 0.1 to 0.9
    2c) In U21:AC21, enter numbers from 0.1 to 0.9
    2d) Select T21:AC30 -> Data Table -> Row input R3 and Column input S3.

    The data table fills in with values, and I can quickly look over the table to see how my win percentage changes with different values for R3 and S3. What I quickly see is that there are only 3 results (given inputs from 0.1 to 0.9) -- 0, 1/8, 1/4. I can also quickly see that there is not a single combination for R3:S3 that results in this max value, but that there is a range of values that result in the maximum, and I can quickly see multiple possible combinations of values that result in a win percentage of 1/4.

    What do you think? Are you required to use Solver for this, or would something like a data table work just as well or better?

  6. #6
    Registered User
    Join Date
    04-20-2015
    Location
    oldsmar
    MS-Off Ver
    2010
    Posts
    4

    Re: Help with Solver

    MrShorty - this is great! I was able to setup the data table and get it to work. Thank you very much for your help!!

+ 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: 5
    Last Post: 10-09-2020, 03:33 AM
  2. Replies: 2
    Last Post: 06-22-2020, 03:14 PM
  3. Replies: 7
    Last Post: 12-31-2016, 12:29 PM
  4. Replies: 1
    Last Post: 04-27-2016, 03:05 PM
  5. Replies: 0
    Last Post: 07-20-2014, 12:45 PM
  6. Can't start Solver. Error message says Solver.xlam already open.
    By DaveHills in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 10-21-2012, 11:02 AM
  7. solver macro + simulation code + not updating solver values
    By sabinemaria in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2012, 11:37 AM

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