1. ## formula to display grade based on value

Hi Champs,

I have A1 and B1

i need to display grades in B1 according to the value in A1.

if A1 is between 0 and 0.99, B1 should display A
if A1 is between 1 and 2.49, B1 should display B
if A1 is between 2.50 and 3.49, B1 should display C
if A1 is between 3.50 and 3.89, B1 should display D
if A1 is between 3.99 and 4.19, B1 should display E
if A1 is between 4.2 and 5.00, B1 should display F
if A1 is between 5.1 and 6.00, B1 should display G  Reply With Quote

2. 1. most direct and thumb solution use chain of if else inside b1 cell. =if(A1<1, A, if(A1<2.5, B, if(A1<3.5))) and so on...
2. use vlookup or hlookup formulas, look their manuals and if you need after looking any help I can explain thoroughly how to implement it
3. I am sure there are many other (better) solutions, others will probably suggest  Reply With Quote

3. i am bit new to formulas.. can u please help me with formula other than lookup  Reply With Quote

4. do not you want to use if-else chaining?  Reply With Quote

5. sure.....  Reply With Quote

6. write this inside B1
=IF(OR(A1<0,A1>6),"Error: A1 should be number between 0 and 6",IF( A1<=0.99,"A",IF(A1<=2.49,"B",IF(A1<=3.49,"C",IF(A1<=3.99,"D",IF(A1<=4.19,"E",IF(A1<=5,"F","G")))))))

this is working I hope there is typo for the line: if A1 is between 3.50 and 3.89, B1 should display D
I wrote for range 3.5-3.99  Reply With Quote

7. Thanks a TON mr. samvelt.

i Got a new scenario and used ur formula ..... great..  Reply With Quote

8. yw  Reply With Quote