Hi All,

I am trying to make a spreadsheet for planning my next hike. In this spreadsheet I want to enter X and Y coordinates and elevation of point A, B, C, etc, than from that I want the spreadsheet to calculate the distance between point A and B, elevatain gain or loss and the time it will take to hike from A to B. So far so good, but I also want the spreadsheet to calculate the compass bearing and it seems I get stuck on some logic functions. Look at te picture below.



Points A to D have the following coordinates:



Now I can calculte the angle between North and the heading from A to B with the 3rd formula. The compass bearing than is 360-'theta'AB.
I can also calculate the angle between North and the heading from B to C, with the same 3rd formula. The compass bearing now is the same as the angle.
Continueing, the bearing from C to D will be 180-'theta'CD and the bearing from D to A will be 180+'theta'DA.

Edited by EinsteinX on 07/13/2007 15:23:01 MDT.

hmmmm part of my question is cut off, strange.

Edited by EinsteinX on 07/13/2007 15:20:19 MDT.

The problem is that all I want to enter in my spreadsheet are X, Y and H (altitude) coordinates, but this doesn't give one algorithm to calculate the correct heading. I need some sort of logic function like this:
IF Yb > Ya AND Xb < Xa THAN BEARab = 360 - 'theta'
IF Yb > Ya AND Xb > Xa THAN BEARab = 'theta'
IF Yb < Ya AND Xb > Xa THAN BEARab = 180 - 'theta'
IF Yb < Ya AND Xb < Xa THAN BEARab = 180 + 'theta'

These logig functions aren't in Excel, but supposedly a bit of programming can be done in Excel, I just don't know how. I have done some other programming though just not in Excel.

Was anybody able to follow what I was saying and do you have a solution for this problem, maybe some Excel logic or an algorithm which will calculate a bearing from coordinates?

Eins

Einstein, compared to your solution of general relativity, this is easy. It's just trig, however I'm confused on one point. You said;

"I want the spreadsheet to calculate the distance between point A and B, elevation gain or loss and the time it will take to hike from A to B"
So, "C" is irrelevant; All you need are the x,y,z coordinates of A and B and your average hiking speed, assuming a straight trail.

Have the spreadsheet calculate the horizontal hypotenuse h using the pythagorean theorem with xB-xA and yB-yA, then calculate the trail distance d using h and zB-zA.
Time will be d/speed.
Use any two of x,y and h with SOHCAHTOA to get theta(true). Then apply declination.

But somehow I think you do want to incorporate C given your diagram; can you explain again?

Edited by Brett1234 on 07/14/2007 00:14:06 MDT.

Well dear Brett, relativity is simple once you realise space-time is curved :D.

I'll try to explain again. Indeed you are right that point C has nothing to do with calculating the bearing from A to B, so lets stop thinking about C for the time being and concentrate on going from A to B.

If we use the 2nd formula to calculate theta-AB, the line Yb-Ya will always be the North-South line. So the 2nd formula will always calculate the angle between North and the line AB. In the picture theta-AB will be 45 degrees. So if we start in a certain point A and we want to walk in a direction between West and North, than using equation 3 we can calculate the bearing by substracting theta-AB from 360: BEARab = 360 - 45 = 335 degrees, huray.

Now arriving in point B we want to walk to point C, which is in NE direction. If we use the same algorithm as above, tetha-BC will be the angle between North and the line BC, which also happens to be 45 degrees. Continueing with the same algorithm, BEARbc = 360 - 45 = 335 degrees. Now when we walk NW like we did when wlaking from A to B the bearing is indeed 335 degrees, but walking NE the bearing should off course be 45 degrees.

So I can't think of one algorithm, like the one above that will alway calculate a correct bearing. In fact, the above algorithm will only calculate a correct bearing when we walk in a direction between East and West (clockwise via South). Walking in a direction between West and East (clockwise via North) the last step in the algorithm should be: BEARab = theta-AB.

So now I am looking for an IF/ELSE function within Excel that calculates a TRUE/FALSE condition on basis of which is determened which equation should be used in the last step of the algortihm.

It could go like this:
IF Xb < Xa THAN BEARab = 360 - tetha-AB ELSE BEARab = tetha-AB

Hope this helps,

Eins

Edited by EinsteinX on 07/16/2007 08:51:14 MDT.

OK, this looks like a job for the 'missing' excel book that should have come with the software. I'll try to assist tomorrow..
Cheers!

Edited by Brett1234 on 07/16/2007 10:43:46 MDT.

For two conditions you can use the Excel IF function as follows:

Assume

Xa = A1
Xb = B1
'theta' = C1

then the Excel formula is:

=IF(B1 < A1,360 - C1,C1)

For more than two conditions you can nest IFs or use MATCH and CHOOSE together much like a select-case structure.

For processing angles in degrees I would suggest using a Modulus 360 function instead of "360-x", so that if you go over 360 or under 0 it "wraps around." So just let the IF decide the case and Mod360 the result as follows:

=MOD(IF(B1 < A1,-C1,C1),360)

As a side note, since a comparison test returns a boolean value, and false = 0, true = 1, you don't even need the IF. Just switch the order of the comparison, multiply the result by two and subtract 1. This will give you 1 or -1, which you then multiply by theta and modulus 360 the whole thing.

=MOD(((A1 < B1)*2 - 1) * C1, 360)

Hope this helps!

Edited by phageghost on 07/16/2007 13:02:26 MDT.

EinsteinX, The math is way beyond me, but does it make a difference that the distance between longitude varies with latitude?

A straightline is seldom the best route between two points.

Why not set your bearing on the compass from the map the same way that you would at an orienteering meet.

Richard, I'm not sure if it would make a difference that the distance between longitude varies with latitude. I guess that over vast distances it would.

The reason why I'm doing all this is because I'm making a website of a trail that doesn't have a website yet and I want people to be able to use this website to plan their trip. On the small variations between points I don't think it would make a difference. We're talking about bearings within 1 x 1 km squares, shouldn't make a significant difference I think.

>A straightline is seldom the best route between two points.<

Yep that's why the trail meanders and that's why I want to give bearings on my site. Besides this calculating is part of the pre-trip fun for me.

>Why not set your bearing on the compass from the map the same way that you would at an orienteering meet.<

I'm not sure how one does that. But I kinda like the idea of leaving at home the map and just taking a list (save weight) with compass bearings and using dead reckoning to measure the distance one has to take on this bearing. Don't worry about me getting lost though, I'll be hiking on a well marked trail, no chances of me getting (too) lost.

Eins

Edited by EinsteinX on 07/16/2007 15:27:45 MDT.

Dylan, many many thanks, your formula did the trick. Seems simple now, I only wish Excel would discribe the IF function a bit better. If you're ever in my country I owe you a beer; I'll take you to my favorite pub where you can choose from a selection of 150 beers, many Belgian.

Oh I didn't see the last formula untill just now, smart, that als seems able to do the trick. The 2nd eqn with the MOD 360 doesn't quite work. It will give the wrong bearing when you're moving with a southward component. Just tested the 3rd eqn and it works like a charm, thanks.

BTW, now you can say that you're way smarter than Einstein, LOL.

Eins, owes Dylan a beer.

EinsteinX,

My perception of your project is that is a fun math challenge, but will not be useful in the field. Elite orienteers navigate from the topographic maps and seldom travel on a compass bearing. One orienteering season I never used a compass just to see if it could be done. I was slower, but not much slower.

Richard, you're probably right. Come to think of it I never use a compass in the field. Just use the map and the usualy excelent waymarks of the trail is more than enough. Even when not walking a trail I only twice needed to use a compass on a two week trip, otherwise the (easy) ridges I walk on generly tend to go only in two direction: the one I want to go and where I just came from. I still think it's a cool feature to have in my spreadsheet though which may be usefull later on for my site but probably won't.

Eins

I've seen hikers successfully navigate with a topo and altimeter, and move along pretty nicely. Easier to get a fix, ****IF**** you know where you're starting. I've done it once on Mt. Humphreys to locate a wreck, and it worked pretty well and quick.