[naviguesser74]

I have lat and long of my position and lat and long of target. Using Excel, I want to calculate direction and distance to target. I know there are web pages that do it for you. I don't need this to be extremely accurate. I am willing to use, for example, where I am that one degree of lat is 69 miles and one degree of long is 52 miles. I can subract point two from pointt one and calculate the hypotenus to calculate distance. The direction is trickier. Four "if/then" statements depeding on which quadrent the target is in? Has any done this? Share the formula?

[wiseye]

try boulter.com/gps/distance/ it will be exact. say thankyou to Fizzy Magic.

[Tharagleb]

wiseye, on Oct 19 2006, 12:23 PM, said:

try boulter.com/gps/distance/ it will be exact. say thankyou to Fizzy Magic.

Yeah except that the OP said:

Quote

Using Excel, I want to calculate direction and distance to target. I know there are web pages that do it for you.

[robertlipe]

Pythagorean works only for portions of the Earth small enough to be considered flat. For all but the most trivial cases, that doesn't work. Consult a formulary like: http://williams.best....net/avform.htm

It looks l9ike you're looking for the first two entries in the 'great circle' chapter.

He seems to even have Excel versions on that page.

[GreyingJay]

If you convert your coordinates to UTM, you can do this pretty easily (assuming that both sets of coordinates are within the same UTM grids).

Here's a little primer I copy/pasted from the Ottawa regional forums (Canada's Capital Cachers):

Quote

Cache coordinates are usually given by latitude and longitude. This is convenient because the coordinates are relatively short and the same format works anywhere in the world. Unfortunately latitude and longitude aren't the most convenient coordinate system for doing geometry. The surface of the earth is (approximately) an ellipsoid, and the formulae for doing trigonometry on the surface of an ellipsoid are quite complicated. The stuff we all learned in high school only works on planes, so we have a problem.

What we need is a way to transfer the lat/long coordinates onto a flat grid that we can then use to do the trigonometry. A function that does that is called a map projection; one of the most common is the Universal Transverse Mercator (UTM) projection, and it's built in to your GPS. By selecting UTM as your location format, the GPS will start using UTM coordinates for all its displays. (On my Garmin eMap, I do Setup->Advanced->Location format->UTM UPS).

A UTM coordinate looks something like this:
18 T 0441492 5020648

What does that mean? Well, 18T is the UTM latitude & longitude zone; it basically defines the origin of the coordinate system (there are 60 longitude zones, each divided into 16 latitude zones). As long as all our calculations are in the same zone, we don't need to worry about it. 0441492 is the easting - the number of metres east of the origin - of the point. 5020648 is the northing - the number of metres north of the equator - of the point. So basically, if we ignore the zone, we have two numbers (441492,5020648) that give us (x,y) coordinates on an ordinary cartesian grid.

What can we do with this? Well, for starters, computing the distance between two points is easy: just use the Pythagorean theorem:
dist = sqrt( (x1-x2)^2 + (y1-y2)^2)

So to find the distance from the previous point to, say, 18T 450371 5030346 is a simple calculation:
dist = sqrt( (450371-441492)^2 + (5030346-5020648)^2)
= 13148.7 metres
and the bearing can also be computed using simple trigonometry:
bearing = arctan((5030346-5020648)/(450371-441492))
= 47.5 degrees

Triangles, circles, angles, everything you learned in high school trigonometry will work with UTM coordinates.

[Marbig's Woodenheads]

naviguesser74, on Oct 20 2006, 06:08 AM, said:

I have lat and long of my position and lat and long of target. Using Excel, I want to calculate direction and distance to target. I know there are web pages that do it for you. I don't need this to be extremely accurate. I am willing to use, for example, where I am that one degree of lat is 69 miles and one degree of long is 52 miles. I can subract point two from pointt one and calculate the hypotenus to calculate distance. The direction is trickier. Four "if/then" statements depeding on which quadrent the target is in? Has any done this? Share the formula?

I googled "latitude longitude distance" in advanced search in xls files only. Found THIS. Has everything you need.

[StarBrand]

Marbig's Woodenheads, on Oct 19 2006, 03:55 PM, said:

naviguesser74, on Oct 20 2006, 06:08 AM, said:

I have lat and long of my position and lat and long of target. Using Excel, I want to calculate direction and distance to target. I know there are web pages that do it for you. I don't need this to be extremely accurate. I am willing to use, for example, where I am that one degree of lat is 69 miles and one degree of long is 52 miles. I can subract point two from pointt one and calculate the hypotenus to calculate distance. The direction is trickier. Four "if/then" statements depeding on which quadrent the target is in? Has any done this? Share the formula?

I googled "latitude longitude distance" in advanced search in xls files only. Found THIS. Has everything you need.

Except you can't see the formulas to integrate into your own spreadsheet.....

[Marbig's Woodenheads]

StarBrand, on Oct 20 2006, 08:04 AM, said:

Marbig's Woodenheads, on Oct 19 2006, 03:55 PM, said:

naviguesser74, on Oct 20 2006, 06:08 AM, said:

I have lat and long of my position and lat and long of target. Using Excel, I want to calculate direction and distance to target. I know there are web pages that do it for you. I don't need this to be extremely accurate. I am willing to use, for example, where I am that one degree of lat is 69 miles and one degree of long is 52 miles. I can subract point two from pointt one and calculate the hypotenus to calculate distance. The direction is trickier. Four "if/then" statements depeding on which quadrent the target is in? Has any done this? Share the formula?

I googled "latitude longitude distance" in advanced search in xls files only. Found THIS. Has everything you need.

Except you can't see the formulas to integrate into your own spreadsheet.....

Not quite. The file is not password protected so hidden columns are able to be accessed and formulas seen.
However the file seems to have its own formula functions in there also which would have to be used in your own file. Not sure on how to do this though.

[Marbig's Woodenheads]

A more simple xls program... no need to bother about the previous.


XLS PROGRAM

This post has been edited by Marbig's Woodenheads: 19 October 2006 - 05:40 PM

[fizzymagic]

I have a VBA version of the Vincenty algorithm that I use myself. Anybody who wants it can have it.

(VBA means Excel can use it)

[res2100]

naviguesser74, on Oct 19 2006, 04:08 PM, said:

I have lat and long of my position and lat and long of target. Using Excel, I want to calculate direction and distance to target. I know there are web pages that do it for you. I don't need this to be extremely accurate. I am willing to use, for example, where I am that one degree of lat is 69 miles and one degree of long is 52 miles. I can subract point two from pointt one and calculate the hypotenus to calculate distance. The direction is trickier. Four "if/then" statements depeding on which quadrent the target is in? Has any done this? Share the formula?

Have a look at the "Mileage Spreadsheet" that I have linked on my web site: http://ca.geocities....geocaching.html I think that may be what you are looking for in Excel. I use it all the time to keep track of the caches I have found. It's got all the formulas in the hidden cells.

[dkwolf]

fizzymagic, on Oct 19 2006, 07:02 PM, said:

I have a VBA version of the Vincenty algorithm that I use myself. Anybody who wants it can have it.

(VBA means Excel can use it)

Hey...wanna send that my way? I took some VBA classes in college, and need to get back up-to-speed on it (use ArcView GIS software at work, and it's VBA-based....would like to write some custom code for it) What better way to brush up on the code than by using it in my hobby

[The Cheeseheads]

Here's a page that has some formulas.

[Tahosa and Sons]

Use the Project a Waypoint on your GPS. Or Set the two points as a route. The GPS wil do the thinking for you.

[worldtraveler]

naviguesser74, on Oct 19 2006, 03:08 PM, said:

I have lat and long of my position and lat and long of target. Using Excel, I want to calculate direction and distance to target. I know there are web pages that do it for you. I don't need this to be extremely accurate. I am willing to use, for example, where I am that one degree of lat is 69 miles and one degree of long is 52 miles. I can subract point two from pointt one and calculate the hypotenus to calculate distance. The direction is trickier. Four "if/then" statements depeding on which quadrent the target is in? Has any done this? Share the formula?

Not wanting to overlook the obvious, are you aware that many GPS receivers (and their accompanying mapping software) have a "measure distance" function that does exactly what you're asking?

edit: Sorry, Tahosa and Sons - looks like we were typing the same thing at the same time!

This post has been edited by worldtraveler: 20 October 2006 - 07:16 AM

[naviguesser74]

Thanks, All, for the info. I just want to make a simple GSAK-type Excel spreadsheet or FileMaker database so that I can manage geocache locations on a Mac. I was able to convert a .gpx file to tab delineated info. With help from the macmap group on Yahoo, I found an Excel distance spreadsheet here:

http://tech.groups.yahoo.com/group/macmap/...er%20Utilities/

Now I just need to do a similar calculation for direction. Distance to next cache will be so small that I am willing to assume flat surface.

[tomfuller & Quill]

My favorite is Ed Williams Javascript Great Circle Calculator. http://williams.best....net/gccalc.htm
Remember there is NO WWW in this address. Remember there is a colon( between degrees and minutes in the format. To become proficient with this try solving my A. Spring mystery cache (GCWE3D).
I learned of this while doing a cache in Gillam county Oregon called "Cold Bailey"
To solve the distance between the point where you are and a visible but inaccesable object without a GPSr, use your compass. 1. Determine azumith to object. 2. Turn exactly 90 degrees (right or left, easiest path)
3. Count your paces. 4. When the azumith to the inaccesable object is 30 degrees different from the original az. the distance along the base of the triangle is half the height of the triangle. The hypotenuse is the square root of 5.
Tom Fuller

This post has been edited by tomfuller & Quill: 20 October 2006 - 09:27 AM

[Flyfishermanbob]

Ah , this is already available.......click here
Waypoint workbench

Its an excellent free piece of software ( ok its set up for UK geocachers but can be tailored to suit your needs....)

Slainge ....

[ZoomZoom]

naviguesser74, on Oct 19 2006, 01:08 PM, said:

I have lat and long of my position and lat and long of target. Using Excel, I want to calculate direction and distance to target. I know there are web pages that do it for you. I don't need this to be extremely accurate. I am willing to use, for example, where I am that one degree of lat is 69 miles and one degree of long is 52 miles. I can subract point two from pointt one and calculate the hypotenus to calculate distance. The direction is trickier. Four "if/then" statements depeding on which quadrent the target is in? Has any done this? Share the formula?

Do you want flat world or round world?

[naviguesser74]

flat is fine with me. to get from one geocache to the next, I am not worried about great circle routes or the curvature of the earth.

[naviguesser74]

in case anyone is interested, i have made progress. fishermanbob is right. waypointworkbench, linked above, does everything i had envisioned, and much more. but i have made a simple excel spreadsheet. i take the lat and long from the downloaded .gpx file and paste into the correct columns. i did 100 from a recent PQ. i do the same with the name of the cache and the corresponding notes and clue. i don't have the geocache alpha/numeric (cryptic) reference, which i am going to need because that is what shows on the map programs that show the cache locations. i also need to figure out how to decode the encoded portion of the notes/clue. anyone know how to do that in excel?

[naviguesser74]

to elaborate a little. my simple spreadsheet starts with a Lat1 & Long1 (i use my home coordinates) and shows the direction and distance to all the included caches (using their respective Lat2 & Long2). then i can find, sort, add a field for "found", date added, date found, etc. etc. if anyone wants it, i'll send via email. hopefully, someone can improve on it. it's not GSAK, that's for sure, but it manages the caches AND it runs on anything that will run excel, unlike GSAK

[Don&Betty]

Geo...com gives you the approximate miles from your zip code to every cache listed. Or, you could put the cache's coordinates into your GPS and set up a GOTO to it, to read, at your home, what it says is the distance you are from it. If you can ignore great circle considerations, if the distances are just 100 miles or so, you could use Pythagorean theorom after figuring X and Y coordinates by figuring miles/miliminute of latitude, which is constant everywhere, and miles/miliminute of longitude, which varies with latitude, it being smaller than that for latitude by the factor of cosine of angle of latitude. Assume Earth's radius is 3956 miles. Mi/mmin latitude = 2(pi)(3956 mi) / 360 deg (60 min/deg)(1000 mmin/min) = 0.001150753754 mi/mmin. At 42 deg latitude, for instance, mi/mmin longitude = 0.001150753754 (cos(42) = 0.008551767 mi/mmin. X coordinate is (lon2 - lon1)(0.001151)(cos((lat2 + lat1)/2). Y coordinate is (lat2-lat1)(0.00855). Distance = sqrt(X*X + Y*Y). I use miliminutes because I assume that when you subtract L2-L1, the result will be thousandths of a minute (I'm usually concerned with feet/mmiliminute, to figure shorter relative positions. You might do better for what you want to do to just figure mi/deg instead. Direction angle is arcsine(Y/X). Good luck.

This post has been edited by Don&Betty: 26 October 2006 - 01:01 PM

[DogFleazJR]

Don&Betty, on Oct 26 2006, 12:55 PM, said:

Geo...com gives you the approximate miles from your zip code to every cache listed. Or, you could put the cache's coordinates into your GPS and set up a GOTO to it, to read, at your home, what it says is the distance you are from it. If you can ignore great circle considerations, if the distances are just 100 miles or so, you could use Pythagorean theorom after figuring X and Y coordinates by figuring miles/miliminute of latitude, which is constant everywhere, and miles/miliminute of longitude, which varies with latitude, it being smaller than that for latitude by the factor of cosine of angle of latitude. Assume Earth's radius is 3956 miles. Mi/mmin latitude = 2(pi)(3956 mi) / 360 deg (60 min/deg)(1000 mmin/min) = 0.001150753754 mi/mmin. At 42 deg latitude, for instance, mi/mmin longitude = 0.001150753754 (cos(42) = 0.008551767 mi/mmin. X coordinate is (lon2 - lon1)(0.001151)(cos((lat2 + lat1)/2). Y coordinate is (lat2-lat1)(0.00855). Distance = sqrt(X*X + Y*Y). I use miliminutes because I assume that when you subtract L2-L1, the result will be thousandths of a minute (I'm usually concerned with feet/mmiliminute, to figure shorter relative positions. You might do better for what you want to do to just figure mi/deg instead. Direction angle is arcsine(Y/X). Good luck.

Bearing is =atan(delta North / delta East). This is equivalent to =asin(delta North / r), where r is the radius (or hypoteneus of the triangle) as determined from the quadratic equation as you outline above.

The easiest way to get around the difference in length between minutes of lat and long is to use UTM coordinates, especially when you are in the same UTM grid. Without converting to UTM or making the corrections as Don&Betty describe above you will get erroneous bearing calculations regardless of how close together the origin and destination are.

The problem with using Excel to calculate bearing is that atan/asin report a value between -pi/2 and +pi/2. Therefore you can only determine which compass quadrant you are in by evaluating the delta East and delta North from your chosen origin.

A further complication is that atan/asin will report a value of 0 deg for due east, when on a compass 0 deg is due north.

These problems can be overcome by using a simple lookup table as below:

The first step is to determine the angle using the asin function (asin reports in radians which must be converted to degrees to be useful)

I. r = sqrt((delta North)^2 + (delta East)^2)

II. angle = degrees(asin( r / delta North))

Then check if the destination is west of the origin

III. Wvalue = if(delta East < 0, "A", "") ; if the destination is west of the origin the value will be A, if east then blank

then check if the destination is south of the origin

IV. Svalue = if(delta North < 0,"B", "") ; if the destination is south of the origin the value will be B, if north then blank

Determine the W/S lookup value

V. WSvalue = concatenate(Wvalue, Svalue) ; this just sticks the results from steps III. and IV. together

then lookup the WSvalue in the following table

[indent]90 (destination is in northeast quad)
A [indent]270 (destination is in northwest quad)
AB [indent]270 (destination is in southwest quad)
B [indent]90 (destination is in southeast quad)

using the following formula

VI. compass bearing = vlookup(WSvalue, lookup table range, 2, 0) - (angle from step II.)

Sounds complicated, but it is five formulas plus a 4x2 lookup. Again, the distances must either be from UTm coords or coords adjusted for distance from the equator.

This post has been edited by DogFleazJR: 29 October 2006 - 12:31 PM

[naviguesser74]

Well, I've made some progress. Trying to use Excel or FileMaker on a Mac to find, sort, and otherwise manage downloaded .gpx cache info. I want to be able to read the hint, clue, description, notes etc. By converting (using GPSVisualizer/GPSBabel) the gpx file into a number of different formats, I have determined that all the info on a cache page comes over in the downloaded .gpx file. But converting it sometimes limits its usefullness. In a .txt file, you can see it is all there but, obviously, you can't do calculations (e.g., distance). In a .pdb file, it won't open with typical Mac apps. In a tab or comma-delineated format, the clue and hint get cut off and contain only the title. What is curious about this one is that a month ago I tried converting gpx into tab-delineated and put it in Excel and I got the full clue/hint in one of the cells. But I can't duplicated that now. Don't know what changed. One of the interesting coversion output formats is html. The converted file opens in Safari. Everything from the cache page comes over, even pictures. And you can "find" the cache you are looking for using its GCXXXX title. I would have prefered using Excel or FileMaker and having all the cache info in one place, but I may resort to using Excel to see a quick list that would include direction and distance from current location so I can plan. I can also import the gpx file into maps and see it that way. Then I could use Safari and the html file to read the clue. Interesting that I can do all this with GeoNiche on a Palm with a BT GPSr, but I am having so much difficulty using just a Mac.

[naviguesser74]

By the way, with help, I was able to include in my Excel spreadsheet calculations for direction and distance between the two lats and longs. That part works great and I would be easy to read in tab-delineated info, but I CAN'T READ THE CLUE!