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

[Haf]

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

 

[Airfix9]

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]

Is it:-

=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.

 

[Haf]

Hi,

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

Thanks

 

[Haf]

Is it:-

=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-comfficeffice" /><o></o>
<o></o>
Thanks <o></o>
<o></o>
Haf<o></o>
<o> </o>

 

[Ruddles]

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]

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

 

[Ruddles]

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:-

=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.

 

[3cupsCoffey]

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]

Use polar coordinates, (r, THETA), and plug THETA (in degrees) into the formula above and that should work.

Cheers,
~ Jim