Setting the score based on a multi-select column

[Archie_Timosenc]

Hey all!

I’ve asked the community in my recent post whether or not it is possible to “score” records based on the value in a multi-select field, and I also needed my formula to calculate the total for all values when the multi-select has 2 or more values. Here’s what @Justin_Barrett came up with and helped me a lot:

IF(FIND("Global", Locale), 1) +
IF(FIND("US", Locale), 0.9) +
IF(FIND("UK", Locale), 0.9) +
IF(FIND("DE", Locale), 0.7) +
IF(FIND("CA", Locale), 0.8) +
IF(FIND("FR", Locale), 0.7) +
IF(FIND("ES", Locale), 0.6) +
IF(FIND("MX", Locale), 0.6) +
IF(FIND("BR", Locale), 0.6) +
IF(FIND("AU", Locale), 0.6) +
IF(FIND("IT", Locale), 0.6)

The way that I expect my records to be scored has slightly changed as so I need to refine the formula - I don’t need it to put the values together, instead, I want it to return the biggest value from the list alone. Here’s an example:

The score per locale is as before:

US - 0.9
UK - 0.9
DE - 0.7
Global - 1
CA - 0.8
FR - 0.7
ES - 0.6
MX - 0.6
BR - 0.6
AU - 0.6
IT - 0.6

When I set several locales to the multi-select column (UK,US,DE,ES), I want the formula to return only the biggest value from the list (in this example it is US and UK, so the returned value would be 0.9). Please let me know how that can be done, thank you!

[Kamille_Parks · Accepted solution]

Well there’s a MAX() function, so adapting your formula should be easy.

Airtable Support

Formula field reference

Formulas may involve functions, numeric operations, logical operations, and text operations that operate on fields. For an overview of formula fields, please refer to the Guide to Formula, Lookup, ...

If you replace all your pluses with commas then the general structure would be:

MAX(
   IF(),
   IF(),
   ...
)

[Archie_Timosenc]

Thanks a lot @Kamille_Parks, that works perfectly!

[Andy_Lin1]

Btw, you may want to break Locale into its own table. The advantage of doing this is that your requests (for finding the total and maximum values) can be taken care of simply by using a rollup field, with a formula as simple as sum(values) or max(values), plus the value of each Locale is easily visible without looking at the formula field. The disadvantage is that you would lose the colour-coding of the Locale options.