Here is a formula I am using to get the RAG status to display either "Green" "Yellow" "Amber" "Red" in a calculated column, it works fine for green but when entering the data in a form the value won't go past yellow, I am hoping somebody can help me, here is the formula

=IF([Form Score]<3,"Green", IF(OR([Form Score]>4,[Form Score]<7),"Yellow", IF(OR([Form Score]>8, [Form Score]<14),"Amber", IF([Form Score]>15,"Red"))))

Any help would be appreciated


Issue with your formula is that - for OR condition of Yellow, one of the condition was always true (i.e. Form score >4) hence it was returning Yellow as output every time.

Solution - Replace OR with AND.

P.S. You are missing some condition like [Form Score] equal to 3,4,7,8,14,15.

  • Hi, thanks that works but I have come across the issue where the numbers change i.e. 0-3 Green 4-7 Yellow, so if I get a score of 4 I should get "Yellow" I now get "NO" I tried the Unicode Character for "is equal to or less than" but SP does not like the character?
    – Mike007
    Jun 2 '17 at 14:52
  • This is what I have now :
    – Mike007
    Jun 2 '17 at 14:53
  • =IF([Form Score]<3,"Green", IF(AND([Form Score]>4,[Form Score]<8),"Yellow", IF(AND([Form Score]>8, [Form Score]<15),"Amber", IF(AND([Form Score]>15,[Form Score]<80),"Red"))))
    – Mike007
    Jun 2 '17 at 14:53
  • use "<=" or ">=" as required. Jun 5 '17 at 5:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.