Can Solver enter text/strings into variable cells?

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

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

I'm I missing something? Normally solver is set to minimize travel routes in order to reduce costs and increase efficiency.

Alf

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

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