1

2 questions in a couple of hours, I'm on a roll.

I have two formula fields which drive a traffic light status on my opportunities. I'm trying to change the criteria that drives them to be a bit more exansive that what it is currently but I'm struggling to get the syntax right, if it's even possible.

Current formula

IF( Probability < 0.26, "Red",
IF( Probability > 0.25 && Probability <=0.75, "Amber", "Green"))

I want to change if to the below but it's not working.

IF(AND( Competitors_Known__c="Competetive",Probability<0.31),"Purple",
IF(AND( Competitors_Known__c="Unknown",Probability<0.31),"Purple",
IF(AND( Competitors_Known__c="Non-Competetive",Probability<0.31),"Red",
IF(Probability>0.49,"Orange",
IF(Probability>0.69,"Amber",
IF(Probability>0.79,"Green",null))))))

I'm thinking that simple nested IFs along with AND just won't cut it so CASE will be required but I can't grasp how to build them together.

4
  • Thanks for the Edit, Mark. I couldn't find the formula tags. – CaptainHaZ Jun 23 '14 at 16:23
  • Since it may be affecting your formula logic and should be corrected, the word "competitive" is spelled incorrectly in your field values. – Mark Pond Jun 23 '14 at 16:24
  • 1
    Your nested-if syntax looks fine. What part of the logic isn't working? What type of field is Competitors_Known__c? – Mark Pond Jun 23 '14 at 16:35
  • I'm an idiot. Not only was I spelling Competitve incorrectly, I was referencing the wrong field. Thanks for pointing out the obvious, MArk. – CaptainHaZ Jun 23 '14 at 16:56
2

I suspect your problem may start here:

IF(Probability>0.49,"Orange",

I think you'll need

IF(AND(Probability>0.49,"Orange", Probability<=0.69 ),

Otherwise, probability of .75 and .82 will all evaluate to true on this line. Likewise, the "Amber" line will need similar alteration.

3
  • 1
    Alternatively, the logic of Green, Amber, and Orange could be reversed, putting the largest first, if Probability is the only criteria which is critical. – Mark Pond Jun 23 '14 at 16:38
  • The original statement works no problem, the issue is with the second one. I have to introduce the second criteria of Competitors_Known__c for two of the possible statuses. That's the bit I'm struggling with. – CaptainHaZ Jun 23 '14 at 16:50
  • As you can probably tell, Stack Exchange is new to me, need to keep up with both comment threads – CaptainHaZ Jun 23 '14 at 16:51

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.