Switch with numerical operator?

[annedoughertynm]

I have a score card with a computed percentage grade (e.g., 84) that I want to turn into an easy letter grade for my stakeholders.

Rather than using a huge nested IF formula I thought I'd try to use SWITCH to compare and create some values.

SWITCH(
  {% GRADE},
    >=93, 'A',
    <=64, 'F'
)

But I keep getting a generic Sorry, there was a problem creating this field. Invalid formula. Please check your formula text. error message.

I can't find anything in the help documents talking about using comparison operators like =, >=, <= with SWITCH.

Is what I'm trying to do even possible or am I stuck with that massive nested IF statement?

Thanks in advance,

Anne

[jsep · Accepted solution]

SWITCH statements do not support expressions to match against.

However, you can use nested IF statements to create the formula you need:

IF(
  {% GRADE} = BLANK(), BLANK(), 
  IF({% GRADE} >= 93, 'A', 
  IF({% GRADE} >= 80, 'B', 
  IF({% GRADE} >= 70, 'C',
  IF({% GRADE} >= 65, 'D', 
  'F'
)))))

Please note that if your "% GRADE" is a "Percent" field, you will need to use percentages in the formula, for example:

IF(
  {% GRADE} = BLANK(), BLANK(), 
  IF({% GRADE} >= 0.93, 'A', 
  IF({% GRADE} >= 0.8, 'B', 
  IF({% GRADE} >= 0.7, 'C',
  IF({% GRADE} >= 0.65, 'D', 
  'F'
)))))​

References:

1. Nested IF statements
2. SWITCH statements
3. Percent field 

I hope this helps! If you need assistance implementing this solution, feel free to schedule a free call with me.

- Juan, Code and No-Code Solutions Expert

[annedoughertynm]

@jsep Good to know about SWITCH.

Thank you for the help with the IF formula. Worked at treat!