Modifying formula / adding dependency


I added a formula to one of our custom fields that calculates any new ARR on an opportunity and revenue line item (especially usefully for renewals with upsells):


However I would like to add one more condition (or exception) to this formula. In case of Renewals (NOT Renewals with upsells) that have a contraction (Year 1 ARR = 10,000 USD / Year 2 ARR = 5,000 USD) I would like the new ARR to be 0 - right now it's negative. 

So is there a way to modify this formula so that it excludes Renewals (Renewals are chosen from another custom field - Opportunity type) or is there a dependency that I can add that will say only calculate New ARR for New Business, Upsells and Renewals with Upsell types?

Thank you!

Parents Reply Children
  • Hi Chris, 

    I tried that, however it's not returning any values now for New ARR. 

    I was wondering - can I add to my formula a condition that will exclude Opportunity type - renewal?:

    ifElse(contains($arr_yes_no_c,"Y"),subtract($discount_price,$renewal_arr_c),"") - would that be the rollupConditionalSum function? How can I exclude (rather than include values?) 

    For my Opportunity type I have: New Business, Upsell, Renewal with Upsell, Renewal, Additional Services - I would like to exclude Renewal and Additional Services

    Thanks for your help!