I'm trying to use Numbers 3.6.1 to display distances in Miles or Km in column 3 based on the names of cities in columns 1 and 2
For Example:
"Boston" is displayed in cell "A1" and "New York" displayed in cell "B1". I'd like cell "C1" to then automatically read "215 Miles"
I haven't been able to figure out how to do this. Any help would be much appreciated. Thank you in advance.
Hi Nicholas,
Where will you store the information necessary for Numbers to calculate these results?
If it is to be a direct calculation of the shortest distance, you will need the geographic location of each of the two cities, plus a formula/algorithm for calculating the great circle distance between those two locations. For Boston-New York, that's about 190 miles.
For Driving distance (215 miles), the calculations are somewhat more complicated. They require access to a large data set comprising a 'routing map' for the area you want to include, then an algorithm that can look up the two cities, determine a driving route between them, find the driving distance for each section of that route, and add them up.
If you place a severe limit on the number of cities, and have access to the 'driving distance' grids that were (and may still be) included with printed paper maps from service stations and/or AAA (in the US), CAA (Canada), the AA (in Britain) and other motorist organizations elsewhere in the world, you might be able to handle this with one or more Lookup tables, and a suitable lookup function in column C.
Otherwise, you could hire Google's development team (or one of several others) to reinvent the wheel, so to speak, and provide you with a standalone application to do this.
Or you could choose to use a more suitable and existing tool. Some choices are MapQuest, OpenStreetMap, Bing Maps, Google Maps, or (Apple) Maps (included in recent versions of OS X).
Regards,
Barry
Thank you very much for taking the time to explain all of this, Barry.
I actually found out how to do this using only "LOOKUP". What I was trying to do was simply having a certain number display corresponding to another cell.
A google search led me to discover that this can be easily done with only the LOOKUP function.
But thank you very much all the same for this wealth of information as this would be critical if I needed to do more complex calculations based on GPS info and other parameters.
Best,
- Nick
HI Nicholas,
Glad to hear I had overestimated the complexity of the project you were taking on; I was beginning to worry you might get swamped by it. 😁
Take a look at VLOOKUP, as well as LOOKUP. The basic difference is that VLOOKUP can be set to require an exact match, while LOOKUP always accepts a 'close match,' defined as the 'largest' value less than or equal to the search value. If your city names are entered manually, LOOKUP could result in some false answers for cities that are mistyped or are not on the list. VLOOKUP, set to 'exact match' will send an error message, which can be trapped by IFERROR, and replaced with a "Not found" message.
General plan for such an error trap is IFERROR(formula,"Not found") where formula is the VLOOKUP formula that provides a result or produces an error.
Regards,
Barry
how do I format a cell to calculate distance based on names of cities in two other cells
