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.

| improve this answer | |
  • 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. – Monica Jagani 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.