As suggested, the VLOOKUP function is best for this sort of thing: have a range of cells with the lower end of each grade and the corresponding grade:
90 A-
93 A
97 A+
Then use a lookup like =VLOOKUP(score, table, 2, true) where "score" is the cell holding the score you want to calculate a grade for, "table" is the score table part of which is shown above, "2" indicates that we want to return the second column of the table and "true" says that the table represents ranges rather than exact values to be matched.
However, if you want to use IF for this, the calculation for an A would be
=IF(AND(score > 92, score < 97), "A", "not an A")
More fully, you'd need something like
=IF(score >= 97, "A+", IF(score >= 93, "A", IF(score >= 90, "A-", "lower than A")))
In practice, where I have "lower than A" you'd have to have all the other options, so it would be something like
=IF(score >= 97, "A+", IF(score >= 93, "A", IF(score >= 90, "A-", IF(score >= 86, "B+", IF(score >= 80, "B", IF(score >= 76, "B-", IF(score >= 70, "C+", IF(score >= 60, "C", IF(score >= 56, "C-", IF(score >= 53, "D+", IF(score >= 48, "D", IF(score >= 45, "D-", "F"))))))))))))
with the numbers obviously depending on where the actual grade points are.
As you can see the vlookup formula is much simpler, and much easier to maintain if the grade points change.
2006-12-31 15:18:12
·
answer #1
·
answered by Scarlet Manuka 7
·
1⤊
0⤋
VLOOKUP and IF THEN statements can work but become difficult to maintain.
An alternative solution would be to create a User Defined Function in VBA. See the following link for information on how to do this:
http://office.microsoft.com/en-us/excel/HA010548461033.aspx?pid=CL100570551033
2007-01-02 01:21:58
·
answer #3
·
answered by Renaud 3
·
0⤊
0⤋
do the following
Step1 : Type your score in A1 cell
Step 2 : Enter the following formula in A2 cell.
you can copy paste the following:
=IF(AND(A1>=90,A1<93),"A-", IF(AND(A1>=93,A1<97),"A", IF(A1>=97,"A+","No Grade")))
2006-12-31 15:13:48
·
answer #4
·
answered by Anonymous
·
1⤊
0⤋
this would be a VLOOKUP. Say your sheets are noted as Sheet1 and Sheet2. Your word is in Sheet1, A1. Your array of information is in Sheet2, A1 to B10. In Sheet1, B1 positioned =VLOOKUP(A1,Sheet2!A1:B10,2,fake) this could return the adjoining cost from Sheet2 to Sheet1. warning: deleting any information from Sheet 2 will invalidate this formulation. To "fix" the information in Sheet1 you may replica, Paste particular, values.
2016-12-11 20:11:12
·
answer #5
·
answered by lot 4
·
0⤊
0⤋