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.
Bookmarks