Skip to main content

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

 

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

 

 

 

@jsep Good to know about SWITCH.

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

Reply


Terms & Conditions