 ShowAll Questionssorted byDate Posted 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.

Swapnil Shawn Reichner 29
Can 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,
))))))

Shawn Swapnil Patne 20
Hi 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 29
Hmm...  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 20
Will try, thanks Shawn! Shawn Reichner 29
No 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 2
Swapnil,

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
)

, Swapnil Patne 20
Hi, 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 20
sorry 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 2
Swapnil,
You may combine the condition with OR || operator like,

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

Hope this helps! Shamsi 110
Similarly, 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 ),