0

I'm sure this is ridiculously simple. I'm trying to put Opportunities into groups based on Age

This is the formula I've written

IF(Opportunity_Age__c   < 100, "Short Term",
IF( OR( Opportunity_Age__c   >= 100,Opportunity_Age__c  <= 249, "Mid Term",
IF( OR( Opportunity_Age__c   >= 250,Opportunity_Age__c  <= 365, "Long Term",
IF( Opportunity_Age__c   >= 366, "365+",
NULL))))))

I'm getting the error below on the third line at "Long Term"

Error: Incorrect parameter type for function 'OR()'. Expected Boolean, received Text

Can someone tell me 1, why I'm getting this error message, and 2, is there a better method?

I thought CASE might work better but I'm not sure if you can use that for ranges

  • you should perhaps do the OR as IF( Opportunity_Age__c >= 250 && Opportunity_Age__c <= 365, "Long Term") ? – akarnid May 15 '19 at 10:43
2

There's no need to deal with AND or OR, because the first matching condition will stop the IF-ELSE chain from continuing.

IF(Opportunity_Age__c < 100, "Short Term",
  IF(Opportunity_Age__c < 250, "Mid Term",
    IF(Opportunity_Age__c < 366, "Long Term", "365+")
  )
)
0

From above formula, the requirement is :

  • age < 100 => 'Short Term'
  • (100 - 249) => 'Mid Term'
  • (250 - 365) =>'Long Term'
  • age >= 366 => '365+'

The formula return type is 'Text' and code is as below:

IF(Opportunity_Age__c   < 100, "Short Term",
IF(AND(Opportunity_Age__c >= 100, Opportunity_Age__c <= 249),"Mid Term",
IF(AND(Opportunity_Age__c >= 250, Opportunity_Age__c <= 365),"Long Term","365+")))
0

You have syntax error in the formula, you are not closing OR, whereas I think you should be using AND instead of OR

This is how your formula should look like

IF(Opportunity_Age__c   < 100, "Short Term",
    IF(AND(Opportunity_Age__c >= 100,Opportunity_Age__c  <= 249), "Mid Term",
        IF(AND(Opportunity_Age__c >= 250,Opportunity_Age__c  <= 365), "Long Term",
            IF(Opportunity_Age__c >= 366, "365+",
                NULL
            )
        )
    )
)

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.