Excel Compass angles in Excel

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?
 

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
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:
Joined
Jan 21, 2017
Messages
16
Reaction score
5
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! :)

Thanks Becky. That's perfect! It's one of those calculations which are intuitive and easy in the head but difficult to formulate.
 

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
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.
 
Joined
Jan 21, 2017
Messages
16
Reaction score
5
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.
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?
 

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
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)
 
Joined
Jan 21, 2017
Messages
16
Reaction score
5
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)
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.
 
Joined
Apr 22, 2010
Messages
5
Reaction score
3
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, ..
 

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
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, ..

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top