Setting the score based on a multi-select column

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!

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

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

MAX(
   IF(),
   IF(),
   ...
)
2 Likes

Thanks a lot @Kamille_Parks, that works perfectly!

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.

1 Like

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.