mardi 19 mars 2019

Google Sheets, Multiple IF, AND Statements with point ranges

So I am using a google spreadsheet to track students points on a math test. I give them a grade based on their points and there end up being a lot of alternatives. We have levelled points so the information below shows first the grade they would get then the total points they got and then the C or higher level points they have.

E   5 pts

D   7 pts 2 c+

C   9 pts 3 c+

B   11pts 5 c+ 

A   13 pts 6 c+

Currently, I have a Google sheet that tallies up there points when I input and I have a column for total points and C or higher points.

I was attempting to do it with multiple IF and AND statements but it isn't working correctly (I end up with the wrong Grade coming out)

Here is the code I tried:

=If(W3<5,"F",IF(AND(W3>=5,X3<2),"E",If(AND(W3>=7,1<X3<3),"D",If(AND(7<W3<9,X3>=2),"D",IF(AND(W3>=9,2<X3<5),"C",IF(AND(8<W3<11,X3>=3),"C",IF(AND(W3>=11,4<X3<6),"B",IF(AND(10<W3<13,X3>=5),"B","A"))))))))

I'm hoping there is an easier way to do this or someone spots my mistake. It isn't the end of the world if it isn't possible because I can just look at the results but it is more fun to be able to figure out how to have it down automatically.

Aucun commentaire:

Enregistrer un commentaire