Help

Switch with numerical operator?

Topic Labels: Formulas
Solved
Jump to Solution
775 2
cancel
Showing results for 
Search instead for 
Did you mean: 
annedoughertynm
5 - Automation Enthusiast
5 - Automation Enthusiast

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

Screenshot 2024-04-29 at 10.25.59.png

 

1 Solution

Accepted Solutions
jsep
7 - App Architect
7 - App Architect

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

 

 

 

See Solution in Thread

2 Replies 2
jsep
7 - App Architect
7 - App Architect

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
5 - Automation Enthusiast
5 - Automation Enthusiast

@jsep Good to know about SWITCH.

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