Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Rung41Rung41 

Optimize Formula Help

I got to imagine there is a more effcient way to compose my formula.  Is there a way to reference "NOT(ISBLANK( Referred_By__c))" once instead of in each IF statement?  

Thanks in advance!

IF(
   ISPICKVAL(Lease_Term__c, '1') && NOT(ISBLANK( Referred_By__c)), 75,
IF(
   ISPICKVAL(Lease_Term__c, '2')&& NOT(ISBLANK( Referred_By__c)), 75,
IF(
   (ISPICKVAL(Lease_Term__c, '3') &&  Amount < 500) && NOT(ISBLANK( Referred_By__c)), 200,
IF(
   (ISPICKVAL(Lease_Term__c, '3') &&  Amount <= 899) && NOT(ISBLANK( Referred_By__c)), 325,
IF(
   (ISPICKVAL(Lease_Term__c, '3') &&  Amount >= 900)&& NOT(ISBLANK( Referred_By__c)), 450,
IF(
   (ISPICKVAL(Lease_Term__c, '6') &&  Amount < 500 && NOT(ISBLANK( Referred_By__c))), 275,
IF(
   (ISPICKVAL(Lease_Term__c, '6') &&  Amount <= 899) && NOT(ISBLANK( Referred_By__c)), 400,
IF(
   (ISPICKVAL(Lease_Term__c, '6') &&  Amount >= 900&& NOT(ISBLANK( Referred_By__c))), 525,
IF(
   (ISPICKVAL(Lease_Term__c, '9') &&  Amount < 500)&& NOT(ISBLANK( Referred_By__c)), 275,
IF(
   (ISPICKVAL(Lease_Term__c, '9') &&  Amount <= 899) && NOT(ISBLANK( Referred_By__c)), 400,
IF(
   (ISPICKVAL(Lease_Term__c, '9') &&  Amount >= 900)&& NOT(ISBLANK( Referred_By__c)), 525,
IF(
   (CONTAINS("12:18:24:36",Text(Lease_Term__c)) &&  Amount < 500) && NOT(ISBLANK( Referred_By__c)), 325,
IF(
   (CONTAINS("12:18:24:36",Text(Lease_Term__c)) &&  Amount <= 899) && NOT(ISBLANK( Referred_By__c)), 450,
IF(
   (CONTAINS("12:18:24:36",Text(Lease_Term__c)) &&  Amount >= 900)&& NOT(ISBLANK( Referred_By__c)), 575,NULL))))))))))))))

 
Best Answer chosen by Rung41
Ramesh DRamesh D
Please try this 
IF(NOT(ISBLANK( Referred_By__c)),
IF((ISPICKVAL(Lease_Term__c, '1')||ISPICKVAL(Lease_Term__c, '2')),75,
IF((ISPICKVAL(Lease_Term__c, '3') &&  Amount < 500), 200,
IF((ISPICKVAL(Lease_Term__c, '3') &&  Amount >= 500 && Amount <= 899), 325,
IF((ISPICKVAL(Lease_Term__c, '3') &&  Amount >= 900), 450,
IF((ISPICKVAL(Lease_Term__c, '6') &&  Amount < 500), 275,
IF((ISPICKVAL(Lease_Term__c, '6') &&  Amount >= 500 && Amount <= 899), 400,
IF((ISPICKVAL(Lease_Term__c, '6') &&  Amount >= 900), 525,
IF((ISPICKVAL(Lease_Term__c, '9') &&  Amount < 500), 275,
IF((ISPICKVAL(Lease_Term__c, '9') &&  Amount >= 500 && Amount <= 899), 400,
IF((ISPICKVAL(Lease_Term__c, '9') &&  Amount >= 900), 525,
IF((CONTAINS("12:18:24:36",Text(Lease_Term__c)) &&  Amount < 500), 325,
IF((CONTAINS("12:18:24:36",Text(Lease_Term__c)) &&  Amount >= 500 && Amount <= 899), 450,
IF((CONTAINS("12:18:24:36",Text(Lease_Term__c)) &&  Amount >= 900), 575,NULL))))))))))))),NULL)
I hope you find the above solution helpful. If it does mark as best answer to help others too.
Thanks,
Ramesh D
 

All Answers

Ramesh DRamesh D
Please try this 
IF(NOT(ISBLANK( Referred_By__c)),
IF((ISPICKVAL(Lease_Term__c, '1')||ISPICKVAL(Lease_Term__c, '2')),75,
IF((ISPICKVAL(Lease_Term__c, '3') &&  Amount < 500), 200,
IF((ISPICKVAL(Lease_Term__c, '3') &&  Amount >= 500 && Amount <= 899), 325,
IF((ISPICKVAL(Lease_Term__c, '3') &&  Amount >= 900), 450,
IF((ISPICKVAL(Lease_Term__c, '6') &&  Amount < 500), 275,
IF((ISPICKVAL(Lease_Term__c, '6') &&  Amount >= 500 && Amount <= 899), 400,
IF((ISPICKVAL(Lease_Term__c, '6') &&  Amount >= 900), 525,
IF((ISPICKVAL(Lease_Term__c, '9') &&  Amount < 500), 275,
IF((ISPICKVAL(Lease_Term__c, '9') &&  Amount >= 500 && Amount <= 899), 400,
IF((ISPICKVAL(Lease_Term__c, '9') &&  Amount >= 900), 525,
IF((CONTAINS("12:18:24:36",Text(Lease_Term__c)) &&  Amount < 500), 325,
IF((CONTAINS("12:18:24:36",Text(Lease_Term__c)) &&  Amount >= 500 && Amount <= 899), 450,
IF((CONTAINS("12:18:24:36",Text(Lease_Term__c)) &&  Amount >= 900), 575,NULL))))))))))))),NULL)
I hope you find the above solution helpful. If it does mark as best answer to help others too.
Thanks,
Ramesh D
 
This was selected as the best answer
Rung41Rung41
Ack...so simple. Thanks for your help!