IF function to apply a number value from a EnumList

AppSheet AppSheet Q&A
1

Hello,

I could use some assistance with developing an IF() statement to calculate the values entered in a different column within the same table. Basically, I have column, AggravatingFactors which is a EnumList type and the following options are available:

None
Strong gang affiliations
Serious injury to victim
Victim is a Senior
Very young or disabled victim
Specific threats to witness or victim
Victim resides in household
Addiction issues
Current emotional state

I need to calculate the values of AggravatingFactors in AggravatingScore and the total score of AggravatingScore will not exceed 3. So, if the user selects just one of the available options, the score will be 1, if the user selects 2 of the available options the score will be 2, if the user selects 3 or more of the available options, the score will be 3. If the user selects “None”, the score will be 0. I’m having trouble figuring out how to write this expression and I have two of these scenarios, so figuring this out will solve both problems. Thank you in advance for your assistance!

2

Maybe this?

IFS(
  NOT(IN("None", [AggravatingFactors])),
    MAX(LIST(3, COUNT([AggravatingFactors])))
)

See also:

2 Likes
3

Or just this:

MAX(LIST(3, COUNT([AggravatingFactors] - LIST("None"))))

If they don’t choose any aggravating factors, or if they choose only None, the result is 0; otherwise, the number is the count of factors that aren’t None, to a max of 3.

See also:

2 Likes
4

That seem to have done it and I applied the same logic to the other scenario so solved both issues! Huge Thanks!

1 Like
5

Thank you so much for your help, When I use either of the expressions you provided, they don’t calculate correctly.

With, MAX(LIST(3, COUNT([AggravatingFactors] - LIST(“None”)))), the score is always “3” even if “None” is selected or no selection at all, seems to be defaulting to 3. If the user selects 1 to 3 options, the score remains 3, when the user selects a 4th, 5th, 6th, etc… the score increases by 1 and should have a max score of 3.

With, IFS(
NOT(IN(“None”, [AggravatingFactors])),
MAX(LIST(3, COUNT([AggravatingFactors])))
)
If “None” is selected, no score will be counted even is the user accidentally selects other options, which is good, can prevent mistakes. But if the user selects 6 other options, the score becomes “6” and the Max is “3”.

I tried to rewrite differently:
IFS(NOT(IN(“None”,[AggravatingFactors])),COUNT([AggravatingFactors])),
IFS([AggravatingFactors]=1,1,
[AggravatingFactors]=2,2,
[AggravatingFactors]=3,3,
[AggravatingFactors]=4,3,
[AggravatingFactors]=5,3,
[AggravatingFactors]=6,3,
[AggravatingFactors]=7,3,
[AggravatingFactors]=8,3)
But have the same issue as the expression above. If “None” is selected, it will not count. But, if “None” is not selected, and all 8 other options are selected, the score becomes “8” and the Max should be 3.

Again, thank you for all your assistance with this!

6

Tom1:

With, MAX(LIST(3, COUNT([AggravatingFactors] - LIST(“None”)))), the score is always “3” even if “None” is selected or no selection at all, seems to be defaulting to 3. If the user selects 1 to 3 options, the score remains 3, when the user selects a 4th, 5th, 6th, etc… the score increases by 1 and should have a max score of 3.

Whoops! My mistake! MAX() should be MIN() instead.

1 Like
7

Yep, that seems to have done it. Thank you!!!

1 Like