+ Start a Discussion
Robbi ConverseRobbi Converse 

Formula field value dependent on picklist selection

Hello! I am trying to create a formula field in which the formula is different depending on a picklist selection. Specifically, if "Weekly" is the picklist selection, the formula will be "Minimum_Charge__c / Weeks__c)*4"; if "Semi-weekly" is selected, the formula will be "Minimum_Charge__c / .05)*4". If neither is selected, I would like for the field to remain blank, but haven't figured that out either. I have the first half working (other than the remain blank part), but can't figure out how to complete it. 

The first part:
(IF(ISPICKVAL( Frequency__c , "Weekly"),  
(Minimum_Charge__c /  CASE(Weeks__c, "Every 2 weeks",2,
                                     "Every 3 Weeks",3,
                                     "Every 4 weeks",4,
                                     "Every 6 weeks",6,
                                     "Every 8 weeks",8,
                                     "Every 10 weeks",10,
                                     "Every 12 weeks",12,
0)*4),0))

How do I complete this? Thanks in advance for your help!
Best Answer chosen by Robbi Converse
Ankur Saini 9Ankur Saini 9
Hi Robbi

try this:
 
(IF(ISPICKVAL( Frequency__c , "Weekly"),  
(Minimum_Charge__c / CASE(Weeks__c, "Every 2 weeks",2,
                                     "Every 3 Weeks",3,
                                     "Every 4 weeks",4,
                                     "Every 6 weeks",6,
                                     "Every 8 weeks",8,
                                     "Every 10 weeks",10,
                                     "Every 12 weeks",12,
0)*4),(IF(ISPICKVAL( Frequency__c , "Semi-weekly"),  
(Minimum_Charge__c /0.05)*4,0))))

Thanks 
Ankur Saini
http://mirketa.com
 

All Answers

Ankur Saini 9Ankur Saini 9
Hi Robbi

try this:
 
(IF(ISPICKVAL( Frequency__c , "Weekly"),  
(Minimum_Charge__c / CASE(Weeks__c, "Every 2 weeks",2,
                                     "Every 3 Weeks",3,
                                     "Every 4 weeks",4,
                                     "Every 6 weeks",6,
                                     "Every 8 weeks",8,
                                     "Every 10 weeks",10,
                                     "Every 12 weeks",12,
0)*4),(IF(ISPICKVAL( Frequency__c , "Semi-weekly"),  
(Minimum_Charge__c /0.05)*4,0))))

Thanks 
Ankur Saini
http://mirketa.com
 
This was selected as the best answer
Robbi ConverseRobbi Converse
Thank you, Ankur!! I would swear that I had tried exactly that, but evidently I had something wrong.