I would like to compare a column based on range of value, it the value matches the range then it should be 1 else 0.
For example: Column Name - Score: contains value ranging from 1 - 10 Now the conditions :- Column Name - Poor Grade: if Value of Score between 1-3, then 1 else 0 Column Name - Good Grade: if Value of Score between 4-6, then 1 else 0 Column Name - Excellent Grade: if Value of Score between 7-10, then 1 else 0
Please Help.
Formula is: If you want result in 1 column then you need to combine all formula in 1 column.
=IF(AND(A3>=1,A3<=3),1,0)
Nested IF statements will work:
=IF(A1=1,"Poor",IF(A1=2,"Poor",IF(A1=3,"Poor",IF(A1=4,"Good",IF(A1=5,"Good",IF(A1=6,"Good",IF(A1=3,"Poor",IF(A1=7,"Excellent",IF(A1=8,"Excellent",IF(A1=9,"Excellent",IF(A1=10,"Excellent","0")))))))))))
Applied specifically to your columns: Poor column =IF(A1=1,1,IF(A1=2,1,IF(A1=3,1,0))) Good column =IF(A1=4,1,IF(A1=5,1,IF(A1=6,1,0))) Excellent column =IF(A1=7,1,IF(A1=8,1,IF(A1=9,1,IF(A1=10,1,0))))
Although VLookUp and Choose may be better options. Case Function Equivalent in Excel
