+ Reply to Thread
Results 1 to 6 of 6

Using Solver to find a percentage with specific constraints

  1. #1
    Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    269

    Using Solver to find a percentage with specific constraints

    I would like to use Solver (or another method, if applicable) to find a percentage of a number based on some specific constraints. I've tried a few variations, but I haven't come up with the right way to tell Solver what I'm looking for. Here's the scenario:

    I was given a target price (Z) of $84,400. I need to find a markup percentage (Y) that results in a cost (X). X * (1 + Y) = Z is the formula if we're starting with a known X. In this case, we're starting with a known Z. The constraints are that both X and Y need to be non-repeating, non-infinite decimals (whole numbers are fine, would prefer limiting decimals to 4 places max). Y also needs to be between .15 (15%) and .25 (25%), and I'm looking to maximize Y.

    I happen to know the answer for this particular number, but I'm looking to have a usable tool when this type of scenario comes up again. Let me know if more information is needed. Thanks!

  2. #2
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,161

    Re: Using Solver to find a percentage with specific constraints

    Y also needs to be between .15 (15%) and .25 (25%), and I'm looking to maximize Y.

    Maximizing Y ? You have already set the range for Y. Oh well perhaps a model like this then:

    Attachment 620510

    Alf

  3. #3
    Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    269

    Re: Using Solver to find a percentage with specific constraints

    Sorry, the site says it's an invalid attachment. And it can either be maximizing X (cost) or Y (markup %), whichever works better as part of a solution.

  4. #4
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,161

    Re: Using Solver to find a percentage with specific constraints

    Second time this happens to me i.e. invalid attachment.

    OK I'll upload the file with solver model.

    Alf
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    269

    Re: Using Solver to find a percentage with specific constraints

    Thanks! I had a feeling the results would be what I'm seeing, which appears to be that Y will always be 25%. I was close with what I was doing, but this showed me what I was missing. I appreciate the help!

  6. #6
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,161

    Re: Using Solver to find a percentage with specific constraints

    You are welcome.

    Thanks for feedback and rep

    Alf

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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