You need to sign in to do that

Don't have an account?

Swapnil Patne 20

# Need help with formula to calculate a amount value

Hi there,

I trying to write a formula which looks at 2 conditions - Blank value & Picklist values and then calculates the amount

Formula statement is -

If Capped Days = 'Null' And

Charge Rate Type = 'Daily' then Contract_value__c = WD__c * DM__c

OR

Charge Rate Type = 'Hourly' then Contract_value__c = WD__C * 8 * DM__c

OR

Charge Rate Type = 'Null' then Contract_value__c = WD__C * DM__c

If Capped Days = 'Not Null' And

Charge Rate Type = 'Daily' then Contract value = Capped_Days__c * DM__c

OR

Charge Rate Type = 'Hourly' then Contract value = Capped_Days__c * 8 * DM__c

OR

Charge Rate Type = 'Null' then Contract_value__c = Capped_Days__C * DM__c

I tried many times but failed to come up a working formula, please can someone help me with it? Any questions please post.

Thanks in advance.

Swapnil

I trying to write a formula which looks at 2 conditions - Blank value & Picklist values and then calculates the amount

Formula statement is -

If Capped Days = 'Null' And

Charge Rate Type = 'Daily' then Contract_value__c = WD__c * DM__c

OR

Charge Rate Type = 'Hourly' then Contract_value__c = WD__C * 8 * DM__c

OR

Charge Rate Type = 'Null' then Contract_value__c = WD__C * DM__c

If Capped Days = 'Not Null' And

Charge Rate Type = 'Daily' then Contract value = Capped_Days__c * DM__c

OR

Charge Rate Type = 'Hourly' then Contract value = Capped_Days__c * 8 * DM__c

OR

Charge Rate Type = 'Null' then Contract_value__c = Capped_Days__C * DM__c

I tried many times but failed to come up a working formula, please can someone help me with it? Any questions please post.

Thanks in advance.

Swapnil

Shawn Reichner 29Can you try...

If(

ISNULL(Capped_Days__c) && ISPICKVAL(Charge_Rate_Type__c, "Daily"),

Contract_value__c = WD__c * DM__c,

If(

ISNULL(Capped_Days__c) && ISPICKVAL(Charge_Rate_Type__c, "Hourly"),

Contract_value__c = WD__C * 8 * DM__c,

If(

ISNULL(Capped_Days__c) && ISPICKVAL(Charge_Rate_Type__c, ""),

Contract_value__c = WD__C * DM__c,

If(

!ISNULL(Capped_Days__c) && ISPICKVAL(Charge_Rate_Type__c, "Daily"),

Contract_value__c = Capped_Days__c * DM__c,

If(

!ISNULL(Capped_Days__c) && ISPICKVAL(Charge_Rate_Type__c, "Hourly"),

Contract_value__c = Capped_Days__c * 8 * DM__c,

If(

!ISNULL(Capped_Days__c) && ISPICKVAL(Charge_Rate_Type__c, ""),

Contract_value__c = Capped_Days__c * DM__c,

))))))

Please mark as best answer if this helps...

Shawn

Swapnil Patne 20Hi Shawn, I tried and got this error- Compiled formula is too big to execute (6,140 characters). Maximum size is 5,000 characters Swapnil Patne 20 Hi shawn, I got this error- Compiled formula is too big to execute (6,140 characters). Maximum size is 5,000 characters Shawn Reichner 29Hmm... Looks like the formula is too complex for a formula field. I woudl now try to accomplish this via the Process builder and I am sure you will be successful there. Swapnil Patne 20Will try, thanks Shawn! Shawn Reichner 29No problem, if the process works for you please select as best answer when you have a moment. If this does nto work, come back and we will look at another option. Malni Chandrasekaran 2Swapnil,

Please try,

CASE(1,

If(ISNULL(Capped_Days__c) && (ISPICKVAL(Charge_Rate_Type__c, "Daily" || ISPICKVAL(Charge_Rate_Type__c, "") ), 1, 0), Contract_value__c = WD__c * DM__c),

if(ISNULL(Capped_Days__c) && ISPICKVAL(Charge_Rate_Type__c, "Hourly"), 1, 0), Contract_value__c = WD__C * 8 * DM__c,

If(!ISNULL(Capped_Days__c) && (ISPICKVAL(Charge_Rate_Type__c, "Daily") || ISPICKVAL(Charge_Rate_Type__c, "")), 1, 0), Contract_value__c = Capped_Days__c * DM__c,

If(!ISNULL(Capped_Days__c) && ISPICKVAL(Charge_Rate_Type__c, "Hourly"), 1, 0), Contract_value__c = Capped_Days__c * 8 * DM__c

)

Please mark it as solved if this solves your problem

,

Swapnil Patne 20Hi, Malni I tried your formula but no luck.. I modified a bit

IF

( AND

(Capped_Days__c = null,

TEXT(Charge_Rate_Type__c)== 'Daily'),(Daily_Margin__c * Working_Days__c) ,

IF(

ISPICKVAL(Charge_Rate_Type__c, 'Hourly'), Duration_in_Hrs__c * Daily_Margin__c,

IF (

ISBLANK(TEXT(Charge_Rate_Type__c)),Daily_Margin__c * Working_Days__c,(Daily_Margin__c * 8 * Working_Days__c)

)

)

)

I get the following Error: Compiled formula is too big to execute (5,494 characters). Maximum size is 5,000 characters

Swapnil Patne 20sorry here is the updated formula.. almost works but one missing condition.. Charge Rate Type = null and Capped Days = null then value = Working_Days__c * Daily_Margin__c (how to include this condition in below formula)

IF

( AND

(TEXT(Charge_Rate_Type__c)== 'Daily', Capped_Days__c != null), (Daily_Margin__c * Capped_Days__c),

IF

( AND

(TEXT(Charge_Rate_Type__c)== 'Daily', Capped_Days__c = null), (Daily_Margin__c * Working_Days__c ),

IF

( AND

(TEXT(Charge_Rate_Type__c)== 'Hourly', Duration_in_Hrs__c != null), (Daily_Margin__c * Duration_in_Hrs__c),

IF (

ISBLANK(TEXT(Charge_Rate_Type__c)), Working_Days__c * Daily_Margin__c, Daily_Margin__c * Capped_Days__c

)

)

)

)

Malni Chandrasekaran 2Swapnil,

You may combine the condition with OR || operator like,

(ISPICKVAL(Charge_Rate_Type__c, "Daily" || ISPICKVAL(Charge_Rate_Type__c, "")

Hope this helps!

Shamsi 110Similarly, add one more if condition at end

IF

( AND

(TEXT(Charge_Rate_Type__c)=null, Capped_Days__c = null), (Working_Days__c *Daily_Margin__c ),

Please mark it as solved if this solves your problem