+ Reply to Thread
Results 1 to 3 of 3

Can Solver enter text/strings into variable cells?

  1. #1
    Registered User
    Join Date
    09-25-2012
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2003
    Posts
    21

    Can Solver enter text/strings into variable cells?

    I'm beginning to doubt that this is possible just by using Solver and my limited knowledge of VBA, but I figured it was worth asking.

    What I'm trying to accomplish is a worksheet that calculates optimal sets of driving routes. I have a sheet of mileages set up as a matrix, with pick-up location names on the Y axis and drop-off location names on the X axis. When drivers are going from a pick-up location to a drop-off location, it's measured as "loaded miles". When they're going from a drop-off location to a pick-up location, they're "unloaded miles". I want solver to maximize "Loaded Miles/Unloaded Miles".

    To do this, it needs to pair sets of 2-3 routes per row. For instance, "A to B", "C to D", and "E to F" will all be on one line. Loaded Miles will be equal to "(A to B)+(C to D)+(E to F)", and unloaded miles will be "(X to A) + (B to C) + (D to E) + (F to X)" with X being a fixed location. The major constraint is that the amount of times each individual location is used must be equal to a number calculated on another worksheet. That's already taken care of, but it's the reason that I'm trying to use Solver for this: the best possible route can't be used in every instance.

    Everything is in place to handle this calculation if I can get Solver to populate the variable cells with strings (or rather, references to strings) in an "A to B" format. The mileage columns will break up the "route" strings that Solver puts into the variable column into "A" and "B" and pull data from the mileage matrix.

    Is there a way to accomplish this? I imagine the actual spreadsheet would be a much better explanation than my rambling. I'll try to get a copy of that up tomorrow.

    Thanks for your time.

  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,501

    Re: Can Solver enter text/strings into variable cells?

    I want solver to maximize "Loaded Miles/Unloaded Miles".
    I'm I missing something? Normally solver is set to minimize travel routes in order to reduce costs and increase efficiency.

    Alf

  3. #3
    Registered User
    Join Date
    09-25-2012
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Can Solver enter text/strings into variable cells?

    Revenue is based on loaded miles, whereas our costs are the same for both loaded and unloaded. Solving for a minimum on "total unloaded miles" would work for me as well. But I'm still facing the same problem: I need Solver to plug these predetermined routes into the spreadsheet in string format (dividing them amongst several rows and in some cases pairing sets of two or three).

    As soon as all those variable cells are filled in with an "A to B" format, the numbers needed to calculate max Loaded/unloaded (or min unloaded miles) will be generated. But given the way it's set up, I realize there's not any way in place for Solver to realize the association between the two.

+ 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