Excel Question

Joe · 09-15-2004, 04:42 PM

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 · 09-15-2004, 04:51 PM

I'd help, but I hate you.

sovereignstar · 09-15-2004, 04:53 PM

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 · 09-15-2004, 04:54 PM

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 · 09-15-2004, 05:03 PM

great, thanks!

Huckleberry · 09-15-2004, 05:18 PM

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

Joe · 09-15-2004, 05:18 PM

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

Huckleberry · 09-15-2004, 05:21 PM

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 · 09-15-2004, 05:22 PM

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 · 09-15-2004, 05:22 PM

dola -

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

Crapshoot · 09-15-2004, 05:23 PM

this particular example is in the excel help itself.

Huckleberry · 09-15-2004, 05:30 PM

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 · 09-15-2004, 05:39 PM

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.

09-15-2004, 04:54 PM	#4
sovereignstar Pro Starter Join Date: Feb 2004	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. Last edited by sovereignstar : 09-15-2004 at 04:55 PM.

09-15-2004, 05:18 PM	#6
Huckleberry College Starter Join Date: Dec 2001	Shouldn't you have a staffer that could help with that? __________________ The one thing all your failed relationships have in common is you. The Barking Carnival (Longhorn-centered sports blog) College Football Adjusted Stats and Ratings

09-15-2004, 05:18 PM	#7
Joe Pro Starter Join Date: Jun 2004 Location: Minneapolis	doh, I guess this wont work with grades such as "A-" or "B+", just straight letter grades Last edited by Joe : 09-15-2004 at 05:19 PM.

09-15-2004, 05:21 PM	#8
Huckleberry College Starter Join Date: Dec 2001	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. __________________ The one thing all your failed relationships have in common is you. The Barking Carnival (Longhorn-centered sports blog) College Football Adjusted Stats and Ratings

09-15-2004, 05:22 PM	#9
gottimd Dearly Missed (9/25/77-12/23/08) Join Date: Nov 2003 Location: DC Suburbs	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. __________________ NAFL New Orleans Saints GM/Co-Commish MP Career Record: 114-85 NAFL Super Bowl XI Champs In memory of Gavin Anthony: 7/22/08-7/26/08

09-15-2004, 04:51 PM	#2
sovereignstar Pro Starter Join Date: Feb 2004	I'd help, but I hate you.

09-15-2004, 04:53 PM	#3
sovereignstar Pro Starter Join Date: Feb 2004	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.

09-15-2004, 05:03 PM	#5
Joe Pro Starter Join Date: Jun 2004 Location: Minneapolis	great, thanks!

09-15-2004, 05:23 PM	#11
Crapshoot Grizzled Veteran Join Date: Dec 2003	this particular example is in the excel help itself.

09-15-2004, 05:30 PM	#12
Huckleberry College Starter Join Date: Dec 2001	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. __________________ The one thing all your failed relationships have in common is you. The Barking Carnival (Longhorn-centered sports blog) College Football Adjusted Stats and Ratings

09-15-2004, 05:39 PM	#13
Joe Pro Starter Join Date: Jun 2004 Location: Minneapolis	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.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)