I am currently working on a spreadsheet in Google Sheets and would like to assign negative numbers a letter grade. For example -10 and lower would be graded an F. I used a similar formula to calculate for positive numbers. However when the values are changed to negatives I receive an #ERROR. Any help is appreciated!
=VLOOKUP(AH9,{-10,"F";-5,"D-";-4,"D";-3,"D+";-1,"C-";0,"C";1,"C+";3,"B-";4,"B";5,"B+"7,"A-";8,"A";10,"A+"},2)
As Rubén pointed out in his comment below the original post, there is a semicolon missing. However, that is not the only issue.
If you are wanting to mark all grades of -10 or lower as an F (thus a -9 marked a D- and so on), you have to structure your list differently:
=ArrayFormula(VLOOKUP(AH9,{-100,"F";-9,"D-";-5,"D";-4,"D+";-2,"C-";0,"C";1,"C+";3,"B-";4,"B";5,"B+";7,"A-";8,"A";10,"A+"},2,TRUE))
Think of each number as X and each X,*grade* pair as "starting at X assign this grade."
I'm guessing you will be wanting to assign more than one person these grades as well. Let's suppose your grades will be assigned in AH2:AH. Clear, say, AI2:AI and then use an array formula in AI2:
=ArrayFormula(IF(AH2:AH="",,IFERROR(VLOOKUP(AH2:AH,{-100,"F";-9,"D-";-5,"D";-4,"D+";-2,"C-";0,"C";1,"C+";3,"B-";4,"B";5,"B+";7,"A-";8,"A";10,"A+"},2,TRUE),"Out Of Range")))
"B+"and7