+ Reply to Thread
Results 1 to 5 of 5

Limiting Variables with Constraints in Solver

  1. #1
    Registered User
    Join Date
    12-12-2018
    Location
    Nashville
    MS-Off Ver
    2016
    Posts
    5

    Limiting Variables with Constraints in Solver

    I am attempting to use Solver to calculate Rates and Hours based upon a total amount when some of the information is know. The issue I'm running into is that the Hours results (which are variable cells) must be limited to the tenth's place. However, solver is returning results well beyond that. Any help in setting up the constraints for this problem would be much appreciated.

    Screenshot.PNG

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Limiting Variables with Constraints in Solver

    Constrain Rate/5 to be integer and Hours * 10 to be integer. Among the zillion possible solutions, ...

    A
    B
    C
    D
    E
    F
    G
    1
    Employee
    Rate/5
    Hours*10
    Rate
    Hours
    Ext
    2
    A
    183
    5
    $915.00
    0.5
    $457.50
    F2: =D2*E2
    3
    C
    103
    131
    $515.00
    13.1
    $6,746.50
    4
    B
    1
    34
    $5.00
    3.4
    $17.00
    5
    D
    1
    34
    $5.00
    3.4
    $17.00
    6
    E
    1
    34
    $5.00
    3.4
    $17.00
    7
    $7,255.00
    F7: =SUM(F2:F6)
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    12-12-2018
    Location
    Nashville
    MS-Off Ver
    2016
    Posts
    5

    Re: Limiting Variables with Constraints in Solver

    I can only set Integer Constraints on the Variable Cells, I thought? How do I constrain the formulas for Rate/5 and Hours*10 cells?

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Limiting Variables with Constraints in Solver

    Col B is the changing variable, constrained to be Integer (evenly divisible by 1). The rate is five times that, which is evenly divisible by ___?

  5. #5
    Registered User
    Join Date
    12-12-2018
    Location
    Nashville
    MS-Off Ver
    2016
    Posts
    5

    Re: Limiting Variables with Constraints in Solver

    Ahhh, got it. Thanks so much.

+ 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. [SOLVED] Excel Solver VBA - Solver Violating Constraints
    By lespaul00 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-19-2015, 04:00 PM
  2. Solver -- limited by number of variables in constraints
    By koochandkai in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-15-2013, 05:04 PM
  3. Solver constraints shall be OR // Solver target cell
    By Cunner in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-30-2009, 04:28 AM
  4. using variables in excel solver constraints problem!
    By 77anders in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 02-12-2009, 08:49 PM
  5. Limiting Constraints in Solver
    By VerifyGuy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-14-2008, 12:46 PM
  6. Replies: 1
    Last Post: 08-08-2006, 01:00 PM
  7. Set solver constraints so variables are either 1 OR 0.
    By Gwyndalf in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-13-2006, 08:20 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