How apply this formula in excel

How to convert the formula in excel. These are the conditions

IF marks=0 then grade ="F"
IF marks=100 then grade="A+"
IF marks>=(xbar+(1.645*sd)) then grade="A+"
IF marks>=(xbar+sd)) and (marks<(xbar+(1.645*sd))) then grade="A"
IF marks>=(xbar+(0.5*sd))) and (marks<(xbar+sd)) then grade="B+"
IF marks>=(xbar)) and (marks<(xbar+(0.5*sd))) then grade="B"
IF marks>=(xbar-(0.5*sd))) and (marks<xbar) then grade="C+"
IF marks>=(xbar-sd)) and(marks<(xbar-(0.5*sd))) then grade="C"
IF marks>=(xbar-(1.645*sd)) and (marks<(xbar-sd)) then grade="D"
IF marks<(xbar-(1.645*sd))  then grade="F"

I have created this formula how to apply this. On cells having numbers only.

=IF($E3="","",
IF($E3=0,"F",
IF($E3=100,"A+",
IF($E3>=($H$2+(1.645*$J$2)),"A+",
IF(AND(($E3>=($H$2+$J$2)),($E3<($H$2+(1.645*$J$2)))),"A",
IF(AND(($E3>=($H$2+(0.5*$J$2))),($E3<($H$2+$J$2))),"B+",
IF(AND(($E3>=($H$2)),($E3<($H$2+(0.5*$J$2)))),"B",
IF(AND(($E3>=($H$2-(0.5*$J$2))),($E3<$H$2)),"C+",
IF(AND(($E3>=($H$2-$J$2)),($E3<($H$2-(0.5*$J$2)))),"C",
IF(AND(($E3>=($H$2-(1.645*$J$2))),($E3<($H$2-$J$2))),"D",
IF(($E3<($H$2-(1.645*$J$2))),"F",)))))))))))

The excel sheet is attached.

I want to apply this formula on column f3.
bba-2.xlsx
searchsanjaysharmaAsked:
Who is Participating?
 
rspahitzConnect With a Mentor Commented:
You could try surrounding the formula with:

IF(ISNUMBER(...),"")

Also, FYI, you could name your cells so they look closer to your original formula, so column E can be called Marks and H2 can be called xbar, etc
0
 
searchsanjaysharmaAuthor Commented:
My question is the formula correct/
0
 
searchsanjaysharmaAuthor Commented:
tx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.