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
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
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
,
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
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
)
)
)
)
You may combine the condition with OR || operator like,
(ISPICKVAL(Charge_Rate_Type__c, "Daily" || ISPICKVAL(Charge_Rate_Type__c, "")
Hope this helps!
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