1

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

Improve this question

1 Answer 1

Reset to default
2

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
4
  • 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
    Commented Jun 2, 2017 at 14:52
  • This is what I have now :
    – Mike007
    Commented Jun 2, 2017 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
    Commented Jun 2, 2017 at 14:53
  • use "<=" or ">=" as required.
    – Monica Jagani
    Commented Jun 5, 2017 at 5:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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