Skip to main content
Solved

Setting the score based on a multi-select column

  • February 14, 2022
  • 3 replies
  • 54 views
A
Forum|alt.badge.img+1

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!

Best answer by Kamille_Parks11

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(),
   ...
)

3 replies

Kamille_Parks11
Forum|alt.badge.img+27

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(),
   ...
)
A
Justin_Barrett
A
Forum|alt.badge.img+1
  • Archie_Timosenc
  • Author
  • Known Participant
  • February 14, 2022

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(),
   ...
)

Thanks a lot @Kamille_Parks, that works perfectly!

A
Forum|alt.badge.img+17
  • Andy_Lin1
  • Inspiring
  • February 14, 2022

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.

Justin_Barrett
Terms & ConditionsAccessibility statement