You need to sign in to do that

Don't have an account?

Ben Knecht

# Need a Formula Ninja

Hi,

I have a formula written in a formula field, and I'm pretty sure the layout is correct, but I can't get the syntax right! I believe there is a parenthesis or comma out of place, but I can't find it and it's driving me nuts. I've tried for over an hour and can't seem to get it. Any help would be greatly, greatly appreciated!

IF(PricebookEntry.Product2.CXO_Service__c=True,

IF(

AND(

ISPICKVAL(Opportunity.New_Renew__c,"Renewal"),

NOT(Product_Family__c="Learning")

)

,

IF(Amount_for_Retention__c>CV_to_Renew__c,Annual_NCVI_Amount__c,

Probable_CV_Retained__c-CV_to_Renew__c)

,

Annual_NCVI_Amount__c))

,

IF(

AND(Opportunity.TA_Migration_Transaction__c=True,ISPICKVAL(PricebookEntry.Product2.Team_Access_Product__c,"Standard"),-1*CV_to_Renew__c)

,

IF(

AND(

ISPICKVAL(Opportunity.New_Renew__c,"Renewal"),

NOT(Product_Family__c="Learning")

)

,

IF(Amount_for_Retention__c>CV_to_Renew__c,Annual_NCVI_Amount__c,

Probable_CV_Retained__c-CV_to_Renew__c)

,

Annual_NCVI_Amount__c)

I have a formula written in a formula field, and I'm pretty sure the layout is correct, but I can't get the syntax right! I believe there is a parenthesis or comma out of place, but I can't find it and it's driving me nuts. I've tried for over an hour and can't seem to get it. Any help would be greatly, greatly appreciated!

**Formula:**IF(PricebookEntry.Product2.CXO_Service__c=True,

IF(

AND(

ISPICKVAL(Opportunity.New_Renew__c,"Renewal"),

NOT(Product_Family__c="Learning")

)

,

IF(Amount_for_Retention__c>CV_to_Renew__c,Annual_NCVI_Amount__c,

Probable_CV_Retained__c-CV_to_Renew__c)

,

Annual_NCVI_Amount__c))

,

IF(

AND(Opportunity.TA_Migration_Transaction__c=True,ISPICKVAL(PricebookEntry.Product2.Team_Access_Product__c,"Standard"),-1*CV_to_Renew__c)

,

IF(

AND(

ISPICKVAL(Opportunity.New_Renew__c,"Renewal"),

NOT(Product_Family__c="Learning")

)

,

IF(Amount_for_Retention__c>CV_to_Renew__c,Annual_NCVI_Amount__c,

Probable_CV_Retained__c-CV_to_Renew__c)

,

Annual_NCVI_Amount__c)

Parker EdelmannWhoops, I copied the wrong version, use this:
Left out a parenthesis.

## All Answers

Doug BeltowskiCan you describe what this formula is meant to be doing? Ben KnechtHi Doug,

Yes, the formula is meant to say:

IF PricebookEntry.Product2.CXO_Service__c=True,

then run this IF statement for logical test is true:

IF(

AND(

ISPICKVAL(Opportunity.New_Renew__c,"Renewal"),

NOT(Product_Family__c="Learning")

)

,

IF(Amount_for_Retention__c>CV_to_Renew__c,Annual_NCVI_Amount__c,

Probable_CV_Retained__c-CV_to_Renew__c)

,

Annual_NCVI_Amount__c))

If the above is false, then run this IF statement for logical test = false:

IF(

AND(Opportunity.TA_Migration_Transaction__c=True,ISPICKVAL(PricebookEntry.Product2.Team_Access_Product__c,"Standard"),-1*CV_to_Renew__c)

,

IF(

AND(

ISPICKVAL(Opportunity.New_Renew__c,"Renewal"),

NOT(Product_Family__c="Learning")

)

,

IF(Amount_for_Retention__c>CV_to_Renew__c,Annual_NCVI_Amount__c,

Probable_CV_Retained__c-CV_to_Renew__c)

,

Annual_NCVI_Amount__c)

I just can't seem to get the syntax right, or I might be missing a closing argument. Any help is greatly appreiated, and please let me know if you need more xontext.

Thank you!!

Parker Edelmann

I was reworking your formula, I've changed some of the indenting and other cosmetic fixes. I noticed on what is now the eighth line, that you had an extra parenthesis I think. I put in another comment on the twelfth line because it doesn't look like you have a third argument for your IF statement that I placed the comment in. After that, I coudn't follow what you're formula is evaluating. It looks to me that the first IF statement has all three arguments, but yet the formula goes on. Did I make a mistake?Thanks,

Parker

Parker EdelmannHold on, I think I got it:
Is this what you're looking for?Ben KnechtYes, that is exactly what I'm looking for! It's still telling me that I'm missing a parenthesis - this is always my problem, finding where the missing piece is it's driving me nuts.
But I think you are really, really close! Parker EdelmannTry this:
I didn't realize that even if you don't have the custom fields, you can still check some of the syntax. This should work in terms of syntax, but it may or may not give you the expected results. Be sure to test it for every outcome before you trust its value.Parker EdelmannWhoops, I copied the wrong version, use this:
Left out a parenthesis.

Ben KnechtParker, you are a life saver, thank you! This works from a syntax standpoint - I'll check all of the scenarios to make sure I've covered my bases. Thank you again!!! Parker EdelmannNo problem! If for some reason it doesn't return the expected result and you need some help to correct it, feel free to reach out to me!

Regards,

Parker