Solved: Formula For Less Than / Greater Than

[Mattster2020]

Morning All,

I have used a formula to try and produce a grade depending on value in the following cells:

A3 = 20.07
C3 = 25.08
E3 = 27.00
G3 = 28.67
I3 = 32.25

The formula is as follows, cell E5 is where the grade is populated.

=IF(E5="","",IF(E5<A$3,"E",IF(E5>A$3<C$3,"D",IF(E5>C$3<E$3,"C",IF(E5>E$3<G$3,"B",IF(E5>I$3,"A"))))))

For some reason the formula doesnt work, can anyone point me where im going wrong?

Regards,

Matt

[Bob Phillips]

I am not sure this is exactly correct as you have 6 statuses and only 5 grades, but it should get you on track

=IF(E5="","",IF(E5<A$3,"E",IF(E5<C$3,"D",IF(E5<E$3,"C",IF(E5<G$3,"B","A")))))

[macropod]

Hi Matt,

Try:
=IF(ISERROR(MATCH(E5,A3:I3)),"",CHAR(69-INT((MATCH(E5,A3:I3)-1)/2)))
Note: this 'promotes; the ranking whenever the vlaue in E5 equals a value in A3:I3 (eg it will return a grading of 'C' when E5=27), but I'm not sure what your intention is.