Excel Compass angles in Excel

T

Tim Rowley

Joined
Jan 21, 2017
Messages
16
Reaction score
5
I'm trying to work with compass angles (0-360 degrees) in Excel. For example, I have angles of 60 in A1 and 300 in A2 and I want to calculate whether to turn CW or ACW to go most efficiently from 60 to 300 degrees. I've been trying to use an IF formula but have been unable to find one that works in all angle combinations. Can anyone please help me?
 
Sorry for the late reply @Tim Rowley! Saw this over the weekend and I've been thinking on it. This solution works:

=IF(AND(A2>A1,ABS(A2-A1)<180),"CW",IF(AND(A2>A1,ABS(A2-A1)>180),"ACW",IF(AND(A1>A2,ABS(A2-A1)<180),"ACW","CW")))

The ABS function returns the absolute value - ie disregards if the number is negative.

Hope this helps! :)
 
Last edited:
Becky said:
Sorry for the late reply @Tim Rowley! Saw this over the weekend and I've been thinking on it. This solution works:

=IF(AND(A2>A1,ABS(A2-A1)<180),"CW",IF(AND(A2>A1,ABS(A2-A1)>180),"ACW",IF(AND(A1>A2,ABS(A2-A1)<180),"ACW","CW")))

The ABS function returns the absolute value - ie disregards if the number is negative.

Hope this helps! :)
Click to expand...

Thanks Becky. That's perfect! It's one of those calculations which are intuitive and easy in the head but difficult to formulate.
 
Forgot to mention - if the difference between the start and end angle is exactly 180 then it will give you either result. Obviously it doesn't really matter which direction is turned, but if you wanted to specify one direction the formula can be changed slightly to incorporate this.
 
Becky said:
Forgot to mention - if the difference between the start and end angle is exactly 180 then it will give you either result. Obviously it doesn't really matter which direction is turned, but if you wanted to specify one direction the formula can be changed slightly to incorporate this.
Click to expand...
Yes, I'd spotted that with the 180 difference and you're right - it doesn't matter. There is one more thing if you don't mind? If there is no value entered in A2 I would ideally like to suppress the answer to the calculation. Is there a simple adjustment to achieve this?
 
Yep, you can just add in another IF function. If you want to make it so that there is no result if either cell A1 or A2 is blank, it would look like this:

=IF(OR(A1="",A2=""),"",IF(AND(A2>A1,ABS(A2-A1)<180),"CW",IF(AND(A2>A1,ABS(A2-A1)>180),"ACW",IF(AND(A1>A2,ABS(A2-A1)<180),"ACW","CW"))))

Alternatively if you just want it to be blank if only A2 is blank, then it would look like this:

=IF(A2="","",IF(AND(A2>A1,ABS(A2-A1)<180),"CW",IF(AND(A2>A1,ABS(A2-A1)>180),"ACW",IF(AND(A1>A2,ABS(A2-A1)<180),"ACW","CW"))))

(bold text shows the changes from my original suggestion)
 
Becky said:
Yep, you can just add in another IF function. If you want to make it so that there is no result if either cell A1 or A2 is blank, it would look like this:

=IF(OR(A1="",A2=""),"",IF(AND(A2>A1,ABS(A2-A1)<180),"CW",IF(AND(A2>A1,ABS(A2-A1)>180),"ACW",IF(AND(A1>A2,ABS(A2-A1)<180),"ACW","CW"))))

Alternatively if you just want it to be blank if only A2 is blank, then it would look like this:

=IF(A2="","",IF(AND(A2>A1,ABS(A2-A1)<180),"CW",IF(AND(A2>A1,ABS(A2-A1)>180),"ACW",IF(AND(A1>A2,ABS(A2-A1)<180),"ACW","CW"))))

(bold text shows the changes from my original suggestion)
Click to expand...
That's absolute magic, Becky! I really must learn more of these functions. At the moment I get totally confused with multiple IF statements and wasted many hours over several days before posting my question. Thanks so much for your help.
 
Hi,

I think, a better, shorter solution is:

=IF(ISNUMBER(A1)*ISNUMBER(A2);IF(MOD(360+A2-A1;360)<MOD(360+A1-A2;360);"CW";"ACW");"fail")

Best regards, ..
 
MaleMan said:
Hi,

I think, a better, shorter solution is:

=IF(ISNUMBER(A1)*ISNUMBER(A2);IF(MOD(360+A2-A1;360)<MOD(360+A1-A2;360);"CW";"ACW");"fail")

Best regards, ..
Click to expand...

Neat way to do it :thumb:

@Tim Rowley bear in mind that in the UK a comma is used as the list separator rather than a semi-colon, so you would need to change each instance.
 
Back
Top