Printable View
Hi friends,
I want to create a formula in Excel 2007 that calculate grade based on percentage for that i have created some formula but was not able to get the exact solution. Example, if the change is <=-7.5%, score is 100. If the change is >=-7.5% and <=-5.0%, score is 98. And so on. Now I could just make a long Nested If to compare the change against my criteria and assign a score. But I hope that somebody here might know a more robust way of doing this.
If you want to Calculate grade based on percentage then i would suggest you to use the following code by pressing F2 and you can also change A1 reference to desired cell.
=IF(AND(A1>=80,A1<= 100),"A",IF(AND(A1>=60,A1<=79) ,"B",IF(AND(A1>=50,A1<=59),"C" ,IF(AND(A1>=40,A1<=49),"D",IF( AND(A1>=1,A1<=39),"E","NO GRADE")))))
I have try to create a formula with the following grades as A if it lies between 95 to 100% , A- if it lies between 90 to 94.9%, B+if it lies between 88 to 89.9%, B if it lies between 83 to 87.9%, B- if it lies between 80 to 82.9%, C+ if it lies between 75 to 79.9% , C if it lies between 70 to 74.9%, D if it lies between 60 to 69.9% , F if it lies between 59.9% and below and insert the following formula into cell A2.
=IF(I2>=95%,"A",IF(I2>=90%,"A- ",IF(I2>=88%,"B+",IF(I2>=83%," B",IF(I2>=80%,"B-","")))))
This formula calculates grades down to B-. If the score is not a B-, then the cell is left blank ("").
In cell B2, fill in this formula:
=IF(A2<>"",A2,IF(I2>=75%,"C+", IF(I2>=70%,"C",IF(I2>=60%,"D", "F"))))
This formulas checks to see if there is a score in Column A (A2<>""). If there is a score, it copies the value in cell A2; otherwise it calculates grades for C-F.