Excel Question

[Joe]

I'm making a spreadsheet for keeping track of the grades for a class of mine, and was wondering if theres any way to put in a function that could automatically calculate the letter grade of a student based on the total from another column? (ie- 92=A, 90=A-, etc.) Anyone know?

[sovereignstar]

I'd help, but I hate you.

[sovereignstar]

Actually, if you can reach the contents of the help file, look under 'if' in the index. There's a very nice example on exactly what you're trying to do.

[sovereignstar]

IF(AverageScore>89,"A",IF(AverageScore>79,"B",
IF(AverageScore>69,"C",IF(AverageScore>59,"D","F"))))

Edit: Just replace 'AverageScore' with the cell name or coordinate.

[Joe]

great, thanks!

[Huckleberry]

Shouldn't you have a staffer that could help with that?

[Joe]

doh, I guess this wont work with grades such as "A-" or "B+", just straight letter grades

[Huckleberry]

It should work with that. Just put a ' in front of the A- or B+ so Excel doesn't try to turn it into a formula.

[gottimd]

Yes it will work with anything....

IF(CellXX>95,"A+", IF(CellXX>91,"A", IF...........

You should go to Mrexcel.com

They have free forum like this that has experts to help out on any issues involving Excel, VBA, Powerpoint, Access, etc.

[Huckleberry]

dola -

Actually, you don't even need to do that. Are you running into the maximum of 6 IF functions within one command?

[Crapshoot]

this particular example is in the excel help itself.

[Huckleberry]

Oops. The maximum is 7 and our guy needs 13 possibilities. The workaround is to set up two cells next to each score and work one off the other.

For example (using 90s = A, 80s = B, 70s = C, 60s = D):

Column A is the number values.

Column B is =IF(A1>96,"A+",IF(A1>93,"A",IF(A1>89,"A-",IF(A1>86,"B+",IF(A1>83,"B",IF(A1>79,"B-",IF(A1>76,"C+",C1)))))))

Column C is =IF(A1>73,"C",IF(A1>69,"C-",IF(A1>66,"D+",IF(A1>63,"D",IF(A1>59,"D-","F")))))

With that, Column B will be your letter grade.

[Joe]

thanks for the help guys, and the link to mrexcel.com. I found the correct formula, it was =LOOKUP(C2,{0;600;670;700;730;770;800;830;870;900;950},{"F";"D";"D+";"C-";"C";"C+";"B-";"B";"B+";"A-";"A"})

I just need to change the total point values for the class.