Mathematical Problem: Calculating rotation from North (Bearing) using conditional statement in formula

[blothian]

Ok here goes...

The facts and background

• I've got a spreadsheet where I have 3 cells which define X,Y,Z coordinates of a known point
• I have a known bearing rotation angle (measured clockwise from North)
• I have a cell which defines a distance
• I have 2 more cells which define a new X,Y coordinate to East of the starting coordinate PERPENDICULAR to the bearing angle
• I have 2 more cells which define a new X,Y coordinate to North of the starting coordinate ALONG the bearing angle
• I have had to use 4 different formulae in each cell in order to report the correct coordinate position which are:
  • X Dest E: =TEXT(B3*COS(RADIANS(A3))+MAI!B7,"0.000000")
  • X Dest N: =TEXT(B3*SIN(RADIANS(A3-180))+MAI!C7,"0.000000")
  • Y Dest E: =TEXT(B3*COS(RADIANS(A3-90))+MAI!B7,"0.000000")
  • Y Dest N: =TEXT(B3*SIN(RADIANS(A3-270))+MAI!C7,"0.000000")
    • B3 = Distance
    • A3 = Bearing Angle
    • MAI!B7 = Starting X Coordinate
    • MAI!C7 = Starting Y Coordinate

I've looked at a lot of google results for how to calculate a coordinate point from a known point with an bearing angle and distance and the above formulae is the result. I also learned that the math(s) is calculated from the Horizontal hence the variable values in the RADIANS() part of the formula. I know they are correct as I can validate the resultant coordinates in CAD software graphically but I do not know if there is a cleaner way of doing it

Now, the problem...

If the known bearing rotation angle is between 90 & 180 degrees, the resultant coordinates are calculated in the wrong place. Specifically they become negative in both directions giving a 'flipped' appearance to the end purpose for what I use the spreadsheet to calculate as used in my CAD Software. Can the above formulae be amended to include an If statement where if the Bearing Angle is between 90° and 270°, the angles have a 180° modifier applied to it? Alternatively, if not in the above formula perhaps another column which generates the adjusted values between the ranges of 270-0-90.

I imagine this will sound quite confusing so if necessary I can draw up some graphics if it helps explain any area that needs more explanation.

Thanks in advance

 

[AlanY]

try this for the new X,Y coordinates,

X, =TEXT(B3*SIN(RADIANS(A3))+MAI!C7,"0.000000")
Y, =TEXT(B3*COS(RADIANS(A3))+MAI!B7,"0.000000")

these should take care of the bearing correctly

 

[blothian]

Hi Alan,

That's not really an option as I need to formula to be repeatable and consistent amongst all cells due to it being copied down using the bottom right of the cell. If I apply those formulae to cells that are currently correct, it would give incorrect results and I don't want to have to hunt through the spreadsheet manually to apply a fix.

I've been trying to work out the if function formula myself and have come up with:
=IF((AND(A7<270,A7>90)),(180+A7),A7)

Using a value of A7 = 164.702 I am getting an incorrect result of 164.702. As I understand it the formula should return 344.702 because 164.702 is less than 270 and greater than 90. Evaluating the formula shows:

Why is it evaluating 164.702 is not less than 270?
If I change the formula to =IF((AND(A7>270,A7>90)),(180+A7),A7) it returns 344.702. What on earth is going on?

 

[AlanY]

Hi Alan,

That's not really an option as I need to formula to be repeatable and consistent amongst all cells due to it being copied down using the bottom right of the cell. If I apply those formulae to cells that are currently correct, it would give incorrect results and I don't want to have to hunt through the spreadsheet manually to apply a fix.

that's the bit that I don't understand.

would you give an example of the input to product the incorrect results?

here are the results from the 2 formula

Excel 2012
	A	B	C	D	E
1	X-org	Y-org
2	0	0
3				X	Y
4	45	10		7.071068	7.071068
5	90	10		10.000000	0.000000
6	135	10		7.071068	-7.071068
7	180	10		0.000000	-10.000000
8	225	10		-7.071068	-7.071068
9	270	10		-10.000000	0.000000
10	315	10		-7.071068	7.071068
11	360	10		0.000000	10.000000
12	300	10		-8.660254	5.000000
13	240	10		-8.660254	-5.000000
14	180	10		0.000000	-10.000000
15	120	10		8.660254	-5.000000
16	60	10		8.660254	5.000000
17	0	10		0.000000	10.000000
MAI

Cell Formulas
Range	Formula
D4	=TEXT(B4*SIN(RADIANS(A4))+MAI!$A$2,"0.000000")
E4	=TEXT(B4*COS(RADIANS(A4))+MAI!$B$2,"0.000000")

 

[blothian]

Sure no problem

Graphical Explanation:

Excel Results (Refer to rows 16 & 17):

To clarify why I need the angles modified if between the range of 90° and 270°, in the first image at the origin is an Red/Green X & Y indicator which represent the origin and direction of a Cartesian coordinate system. The purpose of this spreadsheet is to produce a script which will automate the create of a variety of coordinate systems in a CAD file.

The method of doing so is a 3-step process:

1. Left-click at desired origin point (Source X & Y coordinates)
2. Left-click at point along X-axis (X Direction Dest E & X Direction Dest N)
3. Left-click at point along Y-axis (Y Direction Dest E & Y Direction Dest N). This is only really needed to keep the XY flat (i.e. keeping the Z-height of X & Y the same) because the relationship of X & Y is always 90°.

Following this logic, in the case of the 240 angle, the direction of the X-Axis is from origin then downwards to the left which means the Y axis is down to the right (assuming the Z-Axis is upwards which it is). the 180 modifier ensures the Y-axis always points towards the top of the screen.

I hope that makes sense now.

FWIW, the same test using your revised formula gives the following results (its only the X Direction Columns that matters in this instance). You can see it gives wrong coordinates to the already correct coordinates in row 14. The source X & Y coordinates are X=50967.706 Y=87274.452

 

[blothian]

Can anyone help work out why my formula in the 3rd post is not working - specifically why the first argument of the AND function is giving a wrong answer? I think it has something to do with the fact that the cell in column A is referenced from another sheet because if try the same formula but replace the cell value with the same number but type in the cell, the formula works fine.

 

[Gerald Higgins]

Hi, I don't think the problem is because the value is on a different sheet - that shouldn't normally matter.

I'm not sure but it looks like maybe the formula is reading your value in A7 as a text string, not a number.

Would something like this work ?

=IF((AND((A7+0)<270,(A7+0)>90)),(180+A7),A7+0)

 

[AlanY]

Excel Results (Refer to rows 16 & 17):

FWIW, the same test using your revised formula gives the following results (its only the X Direction Columns that matters in this instance). You can see it gives wrong coordinates to the already correct coordinates in row 14. The source X & Y coordinates are X=50967.706 Y=87274.452

the formula in post#3 yielded the same results as yours (Row14), not quite sure where the wrong coordinates come from.

Excel 2012
	A	B	C	D	E
1	X-org	Y-org
2	50967.706	87274.452
3				X	Y
4	344.823	10		50965.087982	87284.103217
MAI

Cell Formulas
Range	Formula
D4	=TEXT(B4*SIN(RADIANS(A4))+MAI!$A$2,"0.000000")
E4	=TEXT(B4*COS(RADIANS(A4))+MAI!$B$2,"0.000000")