+ Reply to Thread
Results 1 to 2 of 2

Solver dilemma: can variable cells be filled with string values?

  1. #1
    Registered User
    Join Date
    San Antonio, Texas
    MS-Off Ver
    Excel 2003

    Solver dilemma: can variable cells be filled with string values?

    I have a difficult time describing this problem, but I've attached a simplified version of the spreadsheet I'm working on.

    The general premise is that I'm trying to find a way to plan routes based on mileages. I have a series of points scattered across a map, and the 'Miles' worksheet has a matrix detailing the distance in between each point. "Pick up locations are on the Y axis, while "drop off locations" are on the X axis. If a driver is going to run two or three "loads" in a day, he'll have to drive to a pick-up location, take that load to a drop-off location, and then drive to another pick-up location, rinse and repeat.

    Whenever a driver is going from a pick-up location to a drop-off location, it's considered "good mileage". I'm not concerned with minimizing this. When they're going from a drop-off location to a pick-up location, however, this is "bad mileage". I want to minimize this. My goal in solver could be described a couple different ways, but I currently have it set to maximize the ratio of "good miles/bad miles". There are various constraints that I'll add, but I can work through those. The issue I'm having is that I'm not sure how to create an association between mileages and location names that can be used by Solver.

    If you input pick-up and drop-off location names manually into the appropriate boxes on the "Dispatch" page, the mileages will automatically be calculated using the "index" function. So what I'd like Solver to do is to plug these location names into the variable cells (meeting the constraint that the locations are used a certain number of times based on the constraint "L17=0") in different combinations until there's a suitably high number in P14.

    Is this at all possible? Is there a better way to approach this problem?

    Thanks for your time.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user

    Re: Solver dilemma: can variable cells be filled with string values?

    Have set up a Solver solution based on your data. The "Stock delivery Matrix" tells you how many units are moved from the "Pick up" location to the "Drop off" location.

    Object function is set to minimize product of miles times delivered items. The one constraint one could change is the one where the "Sum delivery" is set => than "Stock demand". You could change that to = On the other hand it's sometime an advantage of setting "loose contraints" to get a proper solution and having got that one whould then tighten up the constraints.

    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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


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