Writing a little spread to compute station and offset of a coordinate from two other coordinates.
Having a little problem with determining which direction (right/left) the offset happens to be.
Simply, I inverse A-B (the baseline) and determine an azimuth, then A-C (the offset point) and do a logic test based on whether the new azimuth is larger or not than the original baseline azimuth.
Works fine (maybe) until I get into negative coordinates (and probably not past 360).
Anyone have an elegant solution?
Thanks.
Maybe something like this? My cell B4 is the difference in azimuths. Or put the subtraction into the long formula in place of B4.
=IF( IF( B4>180,B4-360, IF(B4=0,"Right","Left")
use inverse tangent (arctan) of the difference in azimuth and with the distance AC as the hypoteneuse.
If you rig it right the "opposite" is the offset and it will go positiove or negative appropriately.
I think it is necessary to do bit more fiddling to get the correct sign for the station value. I think the ATAN2 function in excel might come into it.
Has been a while since I needed to do it.
[sarcasm]That's easy. Don't work in negative coordinates.[/sarcasm]:-P
You can download my previous effort here it may be a bit over the top but the formulas work.
A lot of the cols are hidden.
https://drive.google.com/file/d/0B2XaZtaJZnbqamgwWHNDWm9scnc/edit?usp=sharing
In simplest terms if the angle formed by base line to offset point is greater than 180 it's left , less than 180 right . Also you can break it down further by saying only do angles greater than 90 for right side otherwise no offset passed 0+00. And greater than 270 for left. You also test distance to see if it passed the end.
Excel Stationing - follow-up
As a follow-up to djames, some specifics -
One of the easier methods for determining "right" or left directions for offsets from a line is to compute the clockwise angle (in a North Azimuth reference frame) from the first to the second line. If that angle is less than 180deg, then the offset is right, greater than 180deg then left.
Dependent upon reference to the spread sheet screen shot presented,
suggest changing the following referenced cells to:
L12: =(ATAN2(L9,M9)+2*PI()*(M9<0))*180/PI()
L18: =(ATAN2(L15,M15)+2*PI()*(M15<0))*180/PI()
K19: clockwise angle
M19: =IF(L12>L18,360+L18-L12,L18-L12)
L21: =L17*COS(M19*PI()/180)
L22: =L17*SIN(M19*PI()/180)
M22: =IF(M19<180,"right","left")
Explanation:
L12 and L18 changes assure the computed azimuth result is between 0 and 360.
M19 change results in a clockwise angle by adding 360 to the azimuth value of the second line if the azimuth value of the first line is less than that of the first.
L21 is the distance from A to C measured along line A-B. Note that this value can be negative if point C is in direction opposite that of from A to B.
L22 is the perpendicular distance from line A-B to point C
M22 results in "right" if the clockwise angle, measured from line A-B to line A-C, is less than 180deg and "left" if greater than or equal to 180.
-----
While I've tested as much as possible, there may be a few tweaks to add. As a friend told me once, there is no such thing as a program without bugs - they just haven't been found yet.
Needed this the other day to calc Z offsets from a reference line on ENZ data provided by another engineer.
Have updated and improved the sheet to do this. I think my formulas in the first one may not have covered all eventualities.
https://drive.google.com/file/d/0B2XaZtaJZnbqOXRXZHZjR2NvNzg/edit?usp=sharing