Wind Direction Formula (wind bearing number to text)

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"
)

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"
)

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"
)

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

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”,
)

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

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

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

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"
)

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: