IF function help in Google Sheets

Question

I am using Goobric to grade assignments for my class. Based on my rubric if students score 1-11 they get a U, 11.5-16.5 they get a PP-, 17-22 a PP, 22.5-27.5 a PP+, 28-33 a P, and 33.5-44 an A. (Standards based grading not normal A,B,C,D,F scale)

I made my function so it no longer gives me an error message; however, it is not giving me the result of 0 even though "Y2" is 24.5, is there something wrong with the formula or do I have to type out every single option between 1-11.

=if(1<Y2<11,"U",if(11.5<Y2<16.5,"PP-",if(17<Y2<22,"PP",if(22.5<Y2<27.5,"PP+",if(28<Y2<33,"P",if(33.5<Y2<44,"A",0))))))

Or is there a simpler way to do what I am trying to do and I'm working too hard? Or possibly what I am trying to do can't be done?

Answer 1

The format

(low<y2<high...)

is what is causing the problem.

There are two solutions:

• replace each

(low<y2<high...)

with

AND(low<y2,y2<high)

... but that is a painful and unnecessary solution.

• Instead simplify the equation and take advantage of the fact that the if statement only applies the first truth it finds

if(Y2<=11,"U",if(Y2<=16.5,"PP-",if(Y2<=22,"PP",if(Y2<=27.5,"PP+",if(Y2<=33,"P",if(Y2<=44,"A",0))))))

Answer 2

It's probably not a bad idea to test the formula in chunks.

So if I just do the first piece, with a 3 in cell Y2 . . . it returns Else.

=if(1<Y2<11,"U","ELSE")

I think the pattern you'd want is something like this.

=if(AND(A1>20,A1<25),"foo","bar")

However, I find trying to write all that really tiring and prone to lots of useless "Parse Error" responses.

I think you'd have a far easier time, just writing out two columns on another sheet somewhere and referencing them via LOOKUP. Column A would have the scores and column B would have the SBG response.

1   U
2   U
3   U
4   U
5   U
6   U
7   U
8   U
9   U
10  U
11  U
12  PP-
13  PP-
14  PP-
15  PP-
16  PP-

etc. etc.

Then you can write something far simpler like

=LOOKUP(Y2,Sheet2!A1:B45)

(Assuming your rubric stuff is on Sheet2).

Or . . . probably the simplest is a VLOOKUP Something like

=VLOOKUP(Y2,{0,"U";11,"PP-";16,"PP";22,"PP+";28,"P";33,"A"},2,1)