Google-sheets – Case statements in SWITCH statements in Google Sheets

google sheets

I'm updating a Google Sheet. In that Google Sheet, I need to display a student's grade based on specific cut offs. I thought a SWITCH statement would be the correct approach. However, based on that documentation, it looks like the case statements are mappings instead of conditionals. This seems incorrect. I feel like I'm misunderstanding something. At this time, I have the following SWITCH statement.

=SWITCH(85, 85<=60, "F", 85<=70, "D", 85<=80, "C", 85<=90, "B", 85<=100, "A", "Unknown")

85 is the value in a cell. I manually put it in to debug. However, this statement always results in "Unknown". Is there a way to use conditional statements in a SWITCH statement?

Thank you.

Best Answer

What you should actually use is the IFS function instead

=IF(LEN(A2),IFS(A2<=60, "F", A2<=70, "D", A2<=80, "C", A2<=90, "B", A2<=100, "A"),"Unknown")

Using the extra function ArrayFormula we apply the same formula to each row for the whole range

=ArrayFormula(IF(LEN(A2:A), 
    IFS(A2:A<=60, "F", A2:A<=70, "D", A2:A<=80, "C", A2:A<=90, "B", A2:A<=100, "A"),""))

enter image description here

Related Topic