+ Reply to Thread
Results 1 to 2 of 2

Excel Solver: looking for multiple solutions

  1. #1
    Registered User
    Join Date
    05-30-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1

    Excel Solver: looking for multiple solutions

    This is my first attempt at Excel's solver.
    Is there a way for solver to give me ALL the possible solutions that fulfill the problem requirements?
    AND even better, can it tell me how many solutions are there?


    Here is a simplified version of my problem

    A + B = X
    Set Objective "X" to Value of 4
    by changing variables "A" & "B" if A & B are integers > 0

    Obviously the solutions are:

    1+3
    2+2
    3+1

    What I would like solver to tell me is:
    - There are 3 possible solutions
    - The solutions are: (1,3) (2,2) and (3,1)

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

    Re: Excel Solver: looking for multiple solutions

    I can see Solver being a useful part of getting to this kind of solution, but Solver is not going to just do this by itself without some input on your part. Here's how I would envision approaching a problem like this.

    1) Solver's part in this kind of problem is going to be as a root finder (Solver is usually very good at finding roots of equations). So the first step in this problem is to use algebra to put the equation into a f(A,B)=0 form. For the simple example given f(A,B)=A+B-X=0. Set up the spreadsheet so that you can enter any two of these variables. In this case, we will put 4 in for X.
    2) We know that A>=1 and an integer, so let's put the first possibility in for A -- 1. Knowing how this function behaves, we expect B will be near 4, so put 4 in for B as an initial guess. Call Solver, tell it to set f equal to a value of 0 by changing B.
    3) Examine the resulting B. If B is an integer and B>=1, then we have found a solution. Decide how you want to "capture" the solution in the spreadsheet.
    4) Go back to step 2, and repeat for the next possible value of A (2), continue repeating steps 2 and 3 with different values for A until you are satisfied that you have found every possible solution.

    Depending on what you did in 3 to capture the solutions, you should now be able to state how many solutions there are and what those solutions are.
    That should be a decent generic solution for a fairly generic question. From there, the details will depend on other constraints or requirements you will put on the project (how much input do you expect from the user, how much is supposed to occur without user input, etc).
    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)

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