Thanks:  0
Likes:  0

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

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

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

2. ## Re: Convert number in (Deg) to direction (Text) N, NNE,...

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.

3. ## Re: Convert number in (Deg) to direction (Text) N, NNE,...

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.

4. ## Re: Convert number in (Deg) to direction (Text) N, NNE,...

Hi,

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

Thanks

5. ## Re: Convert number in (Deg) to direction (Text) N, NNE,...

Originally Posted by Ruddles
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.
[QUOTE=Airfix9;2672201]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.

Thanks

Haf

6. ## Re: Convert number in (Deg) to direction (Text) N, NNE,...

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.

7. ## Re: Convert number in (Deg) to direction (Text) N, NNE,...

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

8. ## Re: Convert number in (Deg) to direction (Text) N, NNE,...

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.

9. ## Re: Convert number in (Deg) to direction (Text) N, NNE,...

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

10. ## Re: Convert number in (Deg) to direction (Text) N, NNE,...

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

Cheers,
~ Jim

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•