Wind Direction Formula (wind bearing number to text)

[Valentino_Escal]

Hi Community!

I am trying to create a formula for wind bearing and convert it to a text.

This is the formula I successfully created in Microsoft Excel and I was trying to follow a similar logic for Airtable…
=CHOOSE(1+ROUND(A2/22.5,0),“N”,“NNE”,“NE”,“ENE”,“E”,“ESE”,“SE”,“SSE”,“S”,“SSW”,“SW”,“WSW”,“W”,“WNW”,“NW”,“NNW”,“N”)

This is the formula I unsuccessfully created for Airtable.
ROUND({field1}/22.5),(“N”,“NNE”,“NE”,“ENE”,“E”,“ESE”,“SE”,“SSE”,“S”,“SSW”,“SW”,“WSW”,“W”,“WNW”,“NW”,“NNW”,“N”))

Note: {field1} is a integer field containing the wind bearing number.

Any ideas??

Thanks!

[Justin_Barrett]

Welcome to the community, @Valentino_Escalona! :grinning_face_with_big_eyes: The closest match to mimicking what you’re doing in Excel would use the SWITCH() function in Airtable. Unlike Excel’s CHOOSE() function, which treats the latter arguments as indexed items, the SWITCH() function is simply looking for a data match, and will switch the output (hence the name) based on the match that it finds.

Here’s how that would look:

SWITCH(
    ROUND({field1} / 22.5),
    1, "N",
    2, "NNE",
    3, "NE",
    4, "ENE",
    5, "E",
    6, "ESE",
    7, "SE",
    8, "SSE",
    9, "S",
    10, "SSW",
    11, "SW",
    12, "WSW",
    13, "W",
    14, "WNW",
    15, "NW",
    16, "NNW",
    17, "N"
)

The SWITCH() function also allows for a final fallback return value if none of the other values match. For example, if the result of the rounded calculation is not in the range of 1-17 for some reason, you could output “Unknown” like this:

SWITCH(
    ROUND({field1} / 22.5),
    1, "N",
    2, "NNE",
    3, "NE",
    4, "ENE",
    5, "E",
    6, "ESE",
    7, "SE",
    8, "SSE",
    9, "S",
    10, "SSW",
    11, "SW",
    12, "WSW",
    13, "W",
    14, "WNW",
    15, "NW",
    16, "NNW",
    17, "N",
    "Unknown"
)

[Valentino_Escal]

Hi @Justin_Barrett ,

Thanks a lot happy to be part of the community.

The SWITCH & ROUND formulas works like a charm!

Thank you so much.

Valentino

[Valentino_Escal]

Hi @Justin_Barrett I just realized this would be more accurate for wind bearing. Would you be able to let me know how to include more than one number (for eg. number 1 and 2 would be NNE)?

SWITCH(
ROUND({field1} / 11.25),
0, “N”,
1,2, “NNE”,
3,4, “NE”,
5,6, “ENE”,
7,8, “E”,
9,10, “ESE”,
11,12, “SE”,
13,14, “SSE”,
15,16, “S”,
17,18, “SSW”,
19,20, “SW”,
21,22, “WSW”,
23,24, “W”,
25,26, “WNW”,
27,28, “NW”,
29,30, “NNW”,
31,32, “N”,
)

[Justin_Barrett]

@Valentino_Escalona Here’s how the SWITCH() function breaks down conceptually:

SWITCH(
    value_to_test,
    matching_value_1, output_1,
    matching_value_2, output_2,
    ...
    [optional default value]
)

As you can see, there’s only one match for each output. To back up a little further, each value passed to a function is called an argument. After the first argument—the one representing the value against which the others will be compared—the remaining arguments (except for the optional final argument) are interpreted in pairs: a matching value and its associated output. If the same output can be tied to more than one matched value, then you’ll need one match-output pair for each:

SWITCH(
    ROUND({field1} / 22.5),
    0, "N",
    1, "NNE",
    2, "NNE",
    3, "NE",
    4, "NE",
    ...
)

That said, you could alter the math a bit and only look for the even numbers from your adjusted range list. The following formula is what I came up with (the “Calculated Direction” field), and it appears to do the job without the need for having two lines for each output option.

SWITCH(
    CEILING((ROUND({Wind Bearing} / 11.25)) / 2) * 2,
    0, "N",
    2, "NNE",
    4, "NE",
    6, "ENE",
    8, "E",
    10, "ESE",
    12, "SE",
    14, "SSE",
    16, "S",
    18, "SSW",
    20, "SW",
    22, "WSW",
    24, "W",
    26, "WNW",
    28, "NW",
    30, "NNW",
    32, "N"
)

NOTE: This also uses a hack (shared by Airtable a while ago) to get the CEILING() function to behave properly.

[Valentino_Escal]

Wow thanks so much @Justin_Barrett!

I used the SWITCH, CEILING, ROUND formulas and works like a charm.

And for all the explanations for each step. It all makes a lot of sense now.

Huge thanks again :ok_hand: