Setting Out Sheet - Rectangular to Polar

Hi All,

In another non-sports related sheet (which makes a change for me), I was at a training course this week and a colleague mentioned some work I'd done (didn't realise that I'd done it though) and that they spotted a substantial typo in it (one figure out of about 160 - and it was out enough that it was obviously wrong, 15 miles wrong on a 200m long site). In the course of the training, I spotted a manual way of adding a check to whenever I do figures like that in future (we don't have the IT to run the software that we have that would remove the need for me to manually input or copy and paste figures in).

In short I have put together a rough example sheet (as once I have the formulas right, I can then apply it to a bigger sheet, do a data validation pick table etc.).

In the attached (which is a two station, 4 point example - the job I made a typo on was about 70 points and 10 stations), my site co-ordinates are in yellow in columns B and C, the distance (to A in this case) in red in D and E is good (which is all I really want to add as a check) - but whilst I was at it, I figured I'd do the bearing as well. The working columns, F to K in green are good (I think, I may have to re-work the quadrant (Q) adjust, I have some papers on my desk - I'm not a surveyor by background).

The column that I need to re-work is L (in blue).

Basically I want to pull whichever check comes up between H and K. For some reason I have L6 and L7 working, but L4 and L5 don't (which makes me think my formula is off (I went with an index, iferror and match?).

I'm sure I can probably tidy the whole sheet up in the long term, but for now I just want to get something workable.

Thanks in advance for any suggestions,

Trig_Problem_Forum Copy.xlsx

You need a 4-quadrant arctangent for bearing. For compass heading (CW from north), E4 and down should be

=MOD(DEGREES(ATAN2(C$2-C4, B$2-B4)), 360)

	A	B	C	D	E
1		x	y	Distance To A	Bearing To A
2	Stn A	130.000	120.000
3	Stn B	140.000	120.000
4	Pt 1	128.753	122.180	2.51	150.2
5	Pt 2	129.539	127.118	7.13	176.3
6	Pt 3	136.946	125.940	9.14	229.5
7	Pt 4	136.160	121.002	6.24	260.8

Originally Posted by mrvp

... therefore it would be approximately 80 degrees from A (if I'm making sense) - assuming up is north

Right -- but the column heading says bearing TO A, not FROM A. If you want FROM,

=MOD(DEGREES(ATAN2(C4-C$2, B4-B$2)), 360)

Originally Posted by mrvp

I'm going to look at this and come back as I think it's me that may have misunderstood (or possibly mislabelled something) and I know you are usually on point.

Originally Posted by shg

Right -- but the column heading says bearing TO A, not FROM A. If you want FROM,

=MOD(DEGREES(ATAN2(C4-C$2, B4-B$2)), 360)

Spot on - (but at least I kind of spotted my problem ). Thanks for taking the time to look at this. From talking with the lecturer that was doing the training in the week, when I talked the idea through I was given the impression that I would need multiple working columns (hence the reason why I was trying to take the approach that I did). I'll re-attach my re-work to this post and mark it solved in a minute, as I found a nice degrees minutes second conversion.

Trig_Problem_Forum Copy_RevA.xlsx

You're welcome.