View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_] Harlan Grove[_2_] is offline
external usenet poster
 
Posts: 1,231
Default exam marks into grades

Teethless mama wrote...
=IF(A1<40,"FAIL",LOOKUP(A1,{40,60,70,80},{"D","C ","B","A"}))


Get rid off IF()

=LOOKUP(A1,{0,40,60,70,80},{"FAIL","D","C","B","A "})

....

Good intention, but fails on garbage and blank cells. Garbage (<0,
100, TRUE/FALSE, text) may but produce correct results. Blank

produces FAIL. Probably closer to the OP's intent to use

=IF(COUNT(A1),LOOKUP(A1,{-9.99999999999999E
+306,0,40,60,70,80,100.000000000001},
{"Invalid","F","D","C","B","A","Invalid"}),"")