What excel formula to type

Question

I am doing an excel sheet for grading system and following is the grade system:

Percentage grade:------------(90-100% )-----------( 80-89%)---------------- (70-79%)--------------------------- Letter Grade:--------------------( A )-----------------------( B )-----------------------( C )-----------------------------

Now, if cell "a1 = 83" than I wanted cell "a2 = B" or if cell "a1 becomes 95" than cell "a2" should become "A" automatically.

I tried this formula: (it didn't work) =(IF(70>a1<79,"C",)),(IF(80>a1<89,"B",)),(IF(90>a1<100,"A",))

Can anyone help me?

Answer 1

Your formula is close, but Excel cannot perform two relational operations in one shot. Instead you have to split it up in an AND statement.

This should work:

= IF(AND(70<=A1,A1<80),"C",IF(AND(80<=A1,A1<90),"B",IF(90<=A1,"A")))

Or for a shorter solution, you can do this:

= IF(A1>=90,"A",IF(A1>=80,"B",IF(A1>=70,"C")))

---

EDIT

Even shorter, completely different method:

= MID("CBA",MATCH(A1,{70,80,90},1),1)

---

EDIT

Just thought of another one, shorter still:

= MID("CBAA",A1/10-6,1)

Answer 2

You want to check the numbers in order:

• If A1 is blank then display a blank.
• If A1 is less than 80% then show "C".
• If A1 is less than 90% then show "B".
• Any other value will be higher than this so show "A".

Translated to a formula:
=IF($A$1="","",IF($A$1<0.8,"C",IF($A$1<0.9,"B","A")))

Answer 3

If possible, split the table of scores/grades into:

LowerScore | UpperScore | Grade

70          79          C

80          89          B

90         100          A 

The use a VLOOKUP formula:

=VLOOKUP(B6,$A$2:$C$3,3,TRUE)

Where B6 is the cell your are inputting your score to, (e.g. the 83).

See here for a very similar question: Value between or search in range and return value in excel

Answer 4

Use index and match (or Lookup).