Grades in Excell 2007
I have a grade column with daily activities and homework averaged. I want to
take that average and show it in a letter grade based on accumulated points. What should I do? |
Grades in Excell 2007
This is a good place to either use a nested IF statement or else a VLOOKUP()
statement. The nested IF is easier to set up as long as you have fewer than 8 breaks in grade points. If you're using straight A through F grading, it's easy: For example, we'll say your numeric average value is in cell B8 and your grade scale is: 95-100 = A 85-94 = B 75-84 = C 65-74 = D anything below 65 = F. Yeah, I know, I'm a tough taskmaster <g With that scale your formula becomes =IF(B8=95,"A",IF(B8=85,"B",IF(B8=75,"C",IF(B8= 65,"D","F")))) or it could be simplified some as =IF(B894,"A",IF(B884,"B",IF(B874,"C",IF(B864," D","F")))) but the simplified version risks giving a higher letter grade (94.1 = A instead of requiring 95 as the first formula does). The nested IF statement has a limitation of 7 levels of nesting in versions of Excel prior to 2007, so if you want to get down to A+,A,A-,B+,B,B-... granularity, you're going to be out of luck. But if you do need that level of reporting, then VLOOKUP() can work for you. First set up a table like this one - it can be on same sheet, or on another in the workbook. The number must go from in ascending order, top with smallest, bottom with largest, and I'm showing column and row identifiers and the numeric grades are the lowest required to achieve the associated letter grade: X Y 1 0 F 2 64 D- 3 67 D 4 70 D+ 5 73 C- 6 76 C 7 79 C+ 8 82 B- 9 85 B 10 88 B+ 11 91 A- 12 94 A 13 97 A+ Once again your average grade is in B8 and your formula becomes (assuming the table is on same sheet) =VLOOKUP(B8,X1:Y13,2,1) if you put the table on another sheet in the same workbook, simply add that sheet's name to the table reference in the formula as: =VLOOKUP(B8,'SomeOther SheetName'!X1:Y13,2,1) Hope this helps - and that you're not quite the hard nosed grader I may appear to be? "PattyCake" wrote: I have a grade column with daily activities and homework averaged. I want to take that average and show it in a letter grade based on accumulated points. What should I do? |
All times are GMT +1. The time now is 08:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com