IF function and validating a value between 2 numbers

[reach78]

Hi All,

I'm trying to write a function that:

1) For values less than 4, the cells will turn red and the following text will populate the cells "Major Improvement Needed"
2) For values between 4 and 5.5 the cells will turn yellow and the following text will populate the cells "Need Improvement"
3) For values 5.5 to 6, cells will turn green and the following text will populate the cells "Good!"

All 3 functions will be in 1 cell....how do I do that?

[benishiryo]

hi there. say your scores are in A1, try this in B1
=IF(A1="","",IF(A1<4,"Major Improvement Needed",IF(A1<5.5,"Need Improvement","Good")))

then assuming your data in A1:B10, select them & go into the Home tab -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
=$B1="Major Improvement Needed"
format Red

repeat the steps & change formula to:
=$B1="Need Improvement"
format yellow

and green is the same.

[reach78]

Thanks benishiryo! That worked. :D

Rep added.