Printable View
Good Morning All,
I am using the below formula to assign a letter based on value of assigned within the formula:
=IF(E5="","",IF(E5<A$3,"C",IF(E5<C$3,"B",IF(E5<E$3,"A",IF(E5>E$3,"A")))))
I want the formula not to populate a letter if the value in E5 is 0, so far I have had no joy developing this.
Any help would be appriecated.
Regards,
Matt
Mattster2020,
Try:
=IF(E5=0,"",IF(E5="","",IF(E5<A$3,"C",IF(E5<C$3,"B",IF(E5<E$3,"A",IF(E5>E$3 ,"A"))))))
I notice in the formula that if the value in E5 < E3 it returns "A"; it also returns "A" if E5 > E3, and returns FALSE if E5 = E3. Could this be intended? I suspect not.
The following might be an answer:
=IF(OR(E5="",E5=0),"",IF(E5>=$E$3,"A*",IF(E5>=$C$3,"A",IF(E5>=$A$3,"B","C") )))
Here I've assumed that you're aiming to give a grading letter to values, like grading examination results, where the letters are in sequence with the marks - A for the higher marks, B,C,D,E etc. for progressively lower marks.
I've taken it that E5 also contains a threshhold and have assigned A* to values equal and above it.
If I've guessed right, then there could be more grades than just 3, in which case we could soon run into the limit of 7 nested Ifs. Another solution is to use Vlookup with a table:
0 C
60 B
70 A
80 A*
this is in K1:L4 for the formula below, and pay particular attention to the order in the left column above and the '2' in red below:
=IF(OR(E5="",E5=0),"",VLOOKUP(E5,$K$1:$L$4,2))
Now you are no longer limited by the number of categories that you want to ascribe letters to.