Formula to return a numerical value from a text difference between 2 cells?

[superkaz661]

Hi All-

First question posted here--I tried hard to search, and didn't find an answer, so I hope this hasn't already been answered.

I'm trying to create a mileage log for my girlfriend. She travels to 7 separate locations, which I will add to a drop-down list.

What I'd like to have is site 1 in (for example) A1. Site 2 would be in A2. I'd like B2 to return the mileage between the two.

Since its a minimal number of sites, the permutations of distances can be relatively easily calculated and listed off worksheet in a separate array if necessary.

But I guess my big question is, can I return a numerical value from a difference between two text cells?

Many Thanks.

[daddylonglegs]

I'd create a table with the sites listed twice, once in A2:A8 and then again in B1:H1

In the cells B2:H8 you can list the mileage between each pair.

If you then have start location in J2 and end location in K2 you get the mileage with

=INDEX(B2:B8,MATCH(J2,A2:A8,0),MATCH(K2,B1:H1,0))

[scottylad2]

You should make a table similar to the one i've attached, which can then be hidden on a seperate sheet or the tab itself can be hidden. Once you've made the table and entered the known mileages, use the index and match Formulas to draw the mileages from the table. If she'll only ever go from Address one to any of the others and back then a table of those can be set instead with a vlookup or similar.

let me know if this helps. I've made a data validation list of the addresses and named the different ranges for ease in looking at the formula

[superkaz661]

I'm truly grateful to both of you. Superb solution that will save her likely over an hour each week. Thank you guys so much.

[superkaz661]

Quick additional question--

I am working with the sheet that scottylad2 provided (awesome--thanks again) and I realized that I also need to add what I'll term an additional "blind" point at the beginning and end of the route.

The question is a two parter-

1. In trying to add an additional row and column to accommodate 8 points instead of 7 (and also included that 8th point in the drop-down list) any time I use that point, excel returns an #N/A. I'm sure Im missing something stupid, but well, sometimes thats the norm for me. How do I fix that?

2. Does anyone have a good idea as to how to incorporate the "blind" (that is, not listed on the sheet) home starting and ending point to get total miles traveled in the day?

Thanks again guys.

[scottylad2]

I'm not sure what your blind points might cover, i'm guessing it's the distance from wherever your gf starts or ends her journey enroute to or from the first and last address? If you have named the ranges similar to what I posted, add extra row or column to these ranges by going to the name manager ctrl+F3 and edit each range, then drag the range to include new column or/and row. you would then fill in the mileages for the blind spots as and when your going to use them I guess, or again if they are know amounts fill in as already shown

[scottylad2]

Hopefully this will point in the right direction. Because your first and last parts are not so easily defined you may want to just enter them manually into the table. Set it up as a fixed set of mileages but as she fills it in if she's done extra miles somewhere then go in and just add them onto the home home x point. At the moment it shows 0, if you have an excess of miles just put it in there and select home home on the dropdown