+ Start a Discussion

Custom Formula Help

Hi I wonder if anyone can help me with a formula


I have a field called "CC after discount" that I want to update from three possibly fields minus a discount field.


Three possible fields are

Legacy Connection charge - Custom field - Currency

Connection Charge - custom field - Roll up summary

Connection Charge FS - Roll up summary

Upgrade additional connection charge - currency field.


I want to take the value from any one of these (which ever one has a value) and minus the amount entered in the field called "Discount Amount connection charge" which is custom field - currency.  and enter the result into the "CC after discount" field.


Thanks in advance for your help



Assuming “CC after discount" is a formula field.


This is the simplest solution as long as you can guarantee only one of the 4 calculations will be greater than zero, otherwise it will add all the results together :



      (Legacy Connection charge - Custom field - Currency)    +

      (Connection Charge - custom field - Roll up summary )  +

      (Connection Charge FS - Roll up summary)                      +

      (Upgrade additional connection charge - currency field)


    - Discount Amount connection charge


If there’s any chance of multiple values, you need to decide which result of the four you want to have precedence.  Assuming they’re listed in the order of preference.


    If(Legacy Connection charge - Custom field - Currency>0,

         Legacy Connection charge - Custom field - Currency,

         If(Connection Charge - custom field - Roll up summary>0,

             Connection Charge - custom field - Roll up summary,

             If(Connection Charge FS - Roll up summary>0,

                 Connection Charge FS - Roll up summary,

                 Upgrade additional connection charge - currency field


    - "Discount Amount connection charge"




Hi Lori

Thanks for your help its really appreciated, I have tried using your second suggestion and have built it as follows, but get the following error.  These confuse me to say the least so any further help you can offer is really appreciated


CC After Discount (Currency) =


IF( Legacy_Connection_Charge__c >0,
         IF( Connection_Charge__c >0,
             IF( Connection_Charge_FS__c >0,
IF(Upgrade_Additional_Connection_Charge__c >0,
                    -  "Discount_Amount_Connection_Charge__c ")))


Error: Incorrect number of parameters for function 'IF()'. Expected 3, received 2



Steve :-/Steve :-/

it looks like you've got a bad paren here 


IF(Upgrade_Additional_Connection_Charge__c >0,
                    -  "Discount_Amount_Connection_Charge__c ")))

Also it looks like you're missing an Else Result at the end