FORMULA TO PICK NUMBER

[Guest]

I am trying to have Excel automatically put in numbers for me relating to
grades. Our grading system follows:

Grade Min % Points
A+ 100 12
A 95-99 11
A- 93-94 10
B+ 91-92 9
B 87-90 8
B- 85-86 7
C+ 83-84 6
C 79-82 5
C- 77-78 4
D+ 75-76 3
D 72-74 2
D- 70-71 1
F 0-69 0

I have a column that I input a student's grade percentage by subject. The
next column I want to create a formula that will choose the student's
percentage (in previous column) and from that percentage input the point that
accurately goes with the percentage grade. For example, if a student has
96.77 in Math, I want to create a formula that will look for the point that
goes with 96.77, which would be 11. Do I need to, first, have the formula
set to round the percentage and then look up what point goes with that
percentage? What would the formula be that I use?

Thank you.

 

[Don Guillett]

Have a look in the help index for LOOKUP, especially vLOOKUP

 

[Guest]

Try this:
=IF(ISNA(MATCH(1,(--LEFT(B2:B14,FIND("-",B2:B14)-1)<=INT(E2))*(--MID(B2:B14,FIND("-",B2:B14)+1,2)>=INT(E2)),0)),12,INDEX(C2:C14,MATCH(1,(--LEFT(B2:B14,FIND("-",B2:B14)-1)<=INT(E2))*(--MID(B2:B14,FIND("-",B2:B14)+1,2)>=INT(E2)),0)))

ctrl+shift+enter (not just enter)

 

[Guest]

What is E2 supposed to stand for?

Try this:
=IF(ISNA(MATCH(1,(--LEFT(B2:B14,FIND("-",B2:B14)-1)<=INT(E2))*(--MID(B2:B14,FIND("-",B2:B14)+1,2)>=INT(E2)),0)),12,INDEX(C2:C14,MATCH(1,(--LEFT(B2:B14,FIND("-",B2:B14)-1)<=INT(E2))*(--MID(B2:B14,FIND("-",B2:B14)+1,2)>=INT(E2)),0)))

ctrl+shift+enter (not just enter)