Determining which side of a semicircle an angle lies.

[kopity]

Hi all,

Please help me create an IF statement to solve the problem I have here. I am given an angle (for interest sake it is a wind direction), say 280 degrees. I then have another angle which might be say 060 degrees.

What I need is a function that can designate "Black" or "Red" determined on what side of the semicircle an angle appears on.

See the picture below as it'll make more sense what I am trying to describe. The angles can all change to be anywhere around the compass, the only constant is that it's always two semicircles.

I'm running into problems as the compass goes around 360 degrees so the heading 050 is actually greater than 359 degrees...
I have tried creating IF statements using the reciprocal of the initial angle but can't make it work perfectly.

Thanks!

[Arkadi]

If I understand this right, then assuming that A1 has the 280 degree value, and B1 has the 60, give this a try:

Please Login or Register  to view this content.

[kopity]

Hi, thanks for your reply!

Your formula works fine if the numbers are set at 280 degrees and 060 degrees.
However, the numbers can change - try substituting 280 with 020 degrees - the formula gives erroneous results.
Any ideas?

[MrShorty]

I tend to solve these kind of problems by referring to the unit circle (see my demo sheet here: https://www.excelforum.com/tips-and-...han-excel.html ). Basically what I see is an attempt to identify which quadrant the angle between the two bearings lies in. You probably need to more carefully define red and black. It looks like black is for quadrants I and II working CCW from the reference angle and red is for quadrants III, and IV. Then I recall from my trig courses way back when the properties of the trig functions in each quadrant and pick a function or functions that will readily tell me which quadrant the angle lies in. In this case, I know that sin() is positive in quadrants I and II, and negative in quadrants III and IV. I would proceed like this:

1) Take the difference of the two angles to get theta. Note that, if I have thought through this correctly, it should not matter when the bearings are on opposite sides of 360. Theta should still represent the correct angle (though you probably should test several cases to be sure).
2) Take the sine of theta. Note that, like all other programming languages, Excel performs trig calculations in radians, not degrees.
3) Test is sine(theta) is positive or negative and assign the correct color.
3a) You may need to think through the two cases where sine(theta) is exactly 0 and decide which color to assign those cases.

At this point, I will assume you are able to program that algorithm into the spreadsheet.

[kopity]

Hi yes your solution seems to have worked thanks!

SIN((B47−B17)×(PI()÷180))

B47 contains the angle that creates the semicircles, B17 is the presented other angle.

I then ran this IF statement on the result; IF(B23<0, "semicircle one", "semicircle two")

I'm not too fussed on when it's right on the boundary between the two semi circles. It's down to less than 1 degree so for the application of this solve it's not necessary to dig any further. Thanks!