1

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?

2 Answers 2

2

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))))))

1
  • Thanks, I wasn't even sure how to write the excel version of "Less than or equal to" that helped a lot and I probably should have just looked up how to do that. Commented Sep 20, 2016 at 19:15
1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.