Convert number in (Deg) to direction (Text) N, NNE,...

Haf

New Member
Joined
Jan 6, 2011
Messages
8
Hello,

I was wondering if it was possible to convert wind direction in Deg (Number) to wind direction in Text (N,NNE,...) using Excel.

Thanks

Haf
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886
This will do the key 16 directions but what do you want to happen if the value in A1 is not exactly the correct direction? Currently, for example, 10 would provide the answer "N".

=LOOKUP(MOD(A1,360),{0,22.5,45,67.5,90,112.5,135,157.5,180,202.5,225,247.5,270,292.5,315,337.5},{"N","NNE","NE","ENE","E","ESE","SE","SSE","S","SSW","SW","WSW","W","WNW","NW","NNW"})

Edit to suit.
 

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
Is it:-
Code:
=CHOOSE(1+ABS(ROUND(A1/22.5,0)),"N","NNE","NE","ENE","E","ESE","SE","SSE","S","SSW","SW","WSW","W","WNW","NW","NNW","N")
where the degrees figure is in A1.

Will cope with any figure but you should verify this calculation yourself before relying on it.
 
Last edited:

Haf

New Member
Joined
Jan 6, 2011
Messages
8
Hi,

Thanks for the tip, It seems to be working but I need to check the claculations.

Thanks
 

Haf

New Member
Joined
Jan 6, 2011
Messages
8

ADVERTISEMENT

Is it:-
Code:
=CHOOSE(1+ABS(ROUND(A1/22.5,0)),"N","NNE","NE","ENE","E","ESE","SE","SSE","S","SSW","SW","WSW","W","WNW","NW","NNW","N")
where the degrees figure is in A1.

Will cope with any figure but you should verify this calculation yourself before relying on it.

This will do the key 16 directions but what do you want to happen if the value in A1 is not exactly the correct direction? Currently, for example, 10 would provide the answer "N".

=LOOKUP(MOD(A1,360),{0,22.5,45,67.5,90,112.5,135,157.5,180,202.5,225,247.5,270,292.5,315,337.5},{"N","NNE","NE","ENE","E","ESE","SE","SSE","S","SSW","SW","WSW","W","WNW","NW","NNW"})

Once again thanks for the suggestions, both formulas work a treat. However, as I’m using a daily wind average direction, it has corrupted some of the direction values. So I think I will be using an hourly average instead which seems to work with both formulas. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Thanks <o:p></o:p>
<o:p></o:p>
Haf<o:p></o:p>
<o:p> </o:p>
 

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
Mine should give "N" for values between 348.75 and 11.25, "NNE" for values between 11.25 and 33.75, etc.

So intermediate values will produce the nearest point.
 

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886

ADVERTISEMENT

I agree, Ruddles' is the better option (and is actually, quite neat).
 

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,786
Thanks. For Haf's benefit, it works by calculating which 1/16th of the compass (22.5°) the direction can be found in and uses the number thus derived to choose between the possible values, adding 1 so that 0° (the first 1/16th) returns the 1st value, etc, and with "N" being repeated at each end of the list because its 1/16th lies half at the beginning of the range and half at the end.

Actually the ABS() isn't necessary - it seems to be left over from when I was trying to cope with 359 degrees being North and getting thoroughly confused!

This will do the job equally well:-
Code:
=CHOOSE(1+ROUND(A1/22.5,0),"N","NNE","NE","ENE","E","ESE","SE","SSE","S","SSW","SW","WSW","W","WNW","NW","NNW","N")

Replace A1 with MOD(A1,360) if values are likely to exceed 360 for any reason.
 
Last edited:

3cupsCoffey

New Member
Joined
Aug 30, 2016
Messages
4
Can this be used for distance of 2 coordinates as well? For instance, I have Point A coordinates, and Point B coordinates. I needed to find the distance between them, which I successfully did in cell E2 but I need to know the cardinal direction of the distance.
Example: Is Point A 20 miles North of Point B? etc etc
The text direction is the only part I have missing for my project
 

jamcall

Active Member
Joined
Sep 19, 2013
Messages
287
Use polar coordinates, (r, THETA), and plug THETA (in degrees) into the formula above and that should work.

Cheers,
~ Jim
 

Watch MrExcel Video

Forum statistics

Threads
1,122,912
Messages
5,598,833
Members
414,260
Latest member
joishe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top