function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Darcie NeibertDarcie Neibert 

Issues with Formula (Case/IF function)

Hi.  I have the following formula.  It works but it doesn't calculate if the "VMS Fee Does Not Apply" is TRUE.  I've tried to re-write the formula using an or statement but I can't get it to execute.  If "VMS Fee Does Not Apply" = True, I want to use 0 in formula.  Appreciate any help!  Thanks

((Simulation_Bill_Rate__c *(1- CASE( RecordTypeId , '012j0000000GUEx',
IF(VMS_Fee_Does_Not_Apply__c=False, Consulting_VMS_Percent__c, NULL),

'012j0000000fn6Y', IF(VMS_Fee_Does_Not_Apply__c=False, Interim_Temp_VMS_Fee__c, NULL), '012j00000015Cl5', IF(VMS_Fee_Does_Not_Apply__c=False, Collabrus_VMS_Fee__c, NULL),
'012j0000000fn6X', IF(VMS_Fee_Does_Not_Apply__c=False, Search_VMS_Fee__c, NULL), NULL))- (Simulation_Pay_Rate__c *(1+ts2__Burden_Pct__c)))/ (Simulation_Bill_Rate__c*(1- CASE( RecordTypeId , '012j0000000GUEx',
IF(VMS_Fee_Does_Not_Apply__c=False, Consulting_VMS_Percent__c, NULL),
'012j0000000fn6Y', IF(VMS_Fee_Does_Not_Apply__c=False, Interim_Temp_VMS_Fee__c,NULL), '012j00000015Cl5', IF(VMS_Fee_Does_Not_Apply__c=False, Collabrus_VMS_Fee__c, NULL),
'012j0000000fn6X', IF(VMS_Fee_Does_Not_Apply__c=False, Search_VMS_Fee__c,NULL), NULL))))
Zachary SingerZachary Singer
Hi Darcie,
Would it make sense to just replace all of the NULL values in your IF() statements with 0? Having a Null value in numeric formulas can cause issues. Though there is an option beneath the formula editor to treat nulls at 0, it's probably easier to avoid them 
(
(
Simulation_Bill_Rate__c *(1-
CASE(RecordTypeId, 
'012j0000000GUEx',IF(VMS_Fee_Does_Not_Apply__c=False, Consulting_VMS_Percent__c, 0), 
'012j0000000fn6Y', IF(VMS_Fee_Does_Not_Apply__c=False, Interim_Temp_VMS_Fee__c, 0), 
'012j00000015Cl5', IF(VMS_Fee_Does_Not_Apply__c=False, Collabrus_VMS_Fee__c, 0), 
'012j0000000fn6X', IF(VMS_Fee_Does_Not_Apply__c=False, Search_VMS_Fee__c, 0), 0))-
(Simulation_Pay_Rate__c *(1+ts2__Burden_Pct__c))
)
/ 
(
Simulation_Bill_Rate__c*(1-
CASE(RecordTypeId, 
'012j0000000GUEx', IF(VMS_Fee_Does_Not_Apply__c=False, Consulting_VMS_Percent__c, 0),
'012j0000000fn6Y', IF(VMS_Fee_Does_Not_Apply__c=False, Interim_Temp_VMS_Fee__c,0), 
'012j00000015Cl5', IF(VMS_Fee_Does_Not_Apply__c=False, Collabrus_VMS_Fee__c, 0),
'012j0000000fn6X', IF(VMS_Fee_Does_Not_Apply__c=False, Search_VMS_Fee__c,0), 0))
)
)