|Everything HP200LX: Knowledge, Products, Service|
Use Lotus and World Time to Figure Distances Between Cities
Several editions of Quick Tips have explained how to calculate the distance between two points on earth using the latitude and longitude of the points (one example is found on pg.57, Vol.3, No.6, 94). These examples make use of the HP Calc Solver function. While useful, this method, which necessitates transcribing the longitudinal and latitudinal values and then keying them in for each location, can be used for any programmable calculator. It ignores the real power of the Palmtop for this application using Lotus 1-2-3 and the Smart Clip functions.
For example, set up the following Lotus spreadsheet MILEAGE.WK1. To do this, open Lotus 1-2-3. In each of the following cells enter:
Cell A1, type: Distance=
Cell A2, leave blank.
Cell A3, type: City A
Cell A4, type: Lat A
Cell A5, type: Long A
Cell A6, type: City B
Cell A7, type: Lat B
Cell A8, type: Long B
Cell B1, enter the formula:
Cell C1, type: Miles
Cell C2, leave blank.
Cell C3, leave blank.
Cell C4, enter: +$b4*2*@PI/360
Cell C5, enter: +$b5*2*@PI/360
Cell C6, leave blank.
Cell C7, enter: +$b7*2*@PI/360
Cell C8, enter: +$b8*2*@PI/360
In the above worksheet, Lat = Latitude, and Long = Longitude. Cell B1 contains the main formula. This formula calculates the distance between two points using an adaptation of a standard formula. The actual longitudes and latitudes in degrees from the HP World-Time list go in cells B4 and B5 for city A, and cells B7 and B8 for city B respectively. Since Lotus calculates trigonometric functions using radians, the translation of degrees into radians is made in cells C4,C5,C7,C8.
NOTE: Don't forget the second parenthesis at the end of the main formula in cell B1.
At the World-Time List, create a customized Smart Clip by pressing: (F5) (Clip) (F2) (Define) (F2) (Field). Select City in the Field Name box, and press (ENTER) (OK) (ENTER) (Hard return). Do the same for Latitude and Longitude: press (F2) (Field), select Latitude, press (ENTER) (OK)(ENTER) (Hard return).
Press (F2) (Field), select Longitude, press (ENTER) (OK)(ENTER) (Hard return). Press (F10) (OK) and name the Smart Clip type Location. Press (F10) (OK). Press (F10) (OK) again to return to the All Cities list.
Now that this custom Smart Clip is defined, choose a city in World Time, press (F5) (Clip), select Location and press (ENTER) (OK). The city name, latitude and longitude are copied to the clipboard.
Now open the Lotus MILEAGE.WK1 spreadsheet and put the cursor on cell B3. Press (Fn) (+) (Paste). The contents of the clipboard are copied to the appropriate cells. Repeat the sequence for the second city, starting in cell B6.
The only nuisance with this process is that the clipboard transfers the latitude and longitude values as text, not numerical data. To rectify this, go into each of the longitude and latitude cells, press (F2) (to edit) (Fn) (HOME) (to get to the beginning of the entry) (DEL) (to eliminate the apostrophe () and allow Lotus to recognize the data as numerical). You could devise a macro to accomplish this, but I didn't include one in this example.) The correct distance in miles will appear in cell B1 after you move the cursor out of the last cell you have corrected.
Copyright © 2010 Thaddeus Computing Inc