Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Katherine SteadKatherine Stead 

Formula Field help

I have a formula field that I need to fix.
current field:  Amount_Minimum__c / Contract_Terms__c

The Contract_Terms__ is an incorrect field and needs to be changed...here's the tricky part
The field I need to change it to is a picklist field
and to top it off it's not a numberical field.

Picklist values
1 Month
2 Months
3 Months
etc up to 18 months. 

So basically I need help building a formula field that allows one field to be divided by the value in the picklist; but the picklist needs to be changed to a number.  I hope this makes sense; can anyone help?
Matthew ReznickMatthew Reznick
This is simple, create a new formula field with the name 'Contract_Terms_number__c' (or something like that) with the following formula.

CASE(Contract_Terms__c,
"1 Month", 1,
"2 Months", 2,
"3 Months", 3,
"4 Months", 4,
"5 Months", 5,
"6 Months", 6,
"7 Months", 7,
"8 Months", 8,
"9 Months", 9,
"10 Months", 10,
"11 Months", 11,
"12 Months", 12,
"13 Months", 13,
"14 Months", 14,
"15 Months", 15,
"16 Months", 16,
"17 Months", 17,
"18 Months", 18,
"None")

also, you may want to ask these type of quesion to @ButtonClickAdmin or go to the formula guru master @Stevemo.

I have tested this formula in my dev org but it is based on a formula field I have in production.  You can also use a Left(Contract_Terms__c,2) 

Matthew 
Katherine SteadKatherine Stead
Thanks so much Matthew. so for the part where I need  the original forumula field to be divided by what ever is in this picklist value would I:

Amount_Minimum__c /CASE(Contract_Terms__c,
"1 Month", 1,
"2 Months", 2,
"3 Months", 3,
"4 Months", 4,
"5 Months", 5,
"6 Months", 6,
"7 Months", 7,
"8 Months", 8,
"9 Months", 9,
"10 Months", 10,
"11 Months", 11,
"12 Months", 12,
"13 Months", 13,
"14 Months", 14,
"15 Months", 15,
"16 Months", 16,
"17 Months", 17,
"18 Months", 18,
"None")

does that make sense?  I think I"m missing something.

Katherine
Matthew ReznickMatthew Reznick
Yes, that will I just built it my dev org and it will work with one slight change.

I created a Formula(Currency) and called it 'Amount Minimum per Month' with this fomula.

Amount_Minimum__c /
CASE(Contract_Terms__c,
"1 Month", 1,
"2 Months", 2,
"3 Months", 3,
"4 Months", 4,
"5 Months", 5,
"6 Months", 6,
"7 Months", 7,
"8 Months", 8,
"9 Months", 9,
"10 Months", 10,
"11 Months", 11,
"12 Months", 12,
"13 Months", 13,
"14 Months", 14,
"15 Months", 15,
"16 Months", 16,
"17 Months", 17,
"18 Months", 18,
NULL)
Ramakrishnan AyyanarRamakrishnan Ayyanar
use this one

 Amount_Minimum__c /
CASE(Contract_Terms__c,
"1 Month", 1,
"2 Months", 2,
"3 Months", 3,
"4 Months", 4,
"5 Months", 5,
"6 Months", 6,
"7 Months", 7,
"8 Months", 8,
"9 Months", 9,
"10 Months", 10,
"11 Months", 11,
"12 Months", 12,
"13 Months", 13,
"14 Months", 14,
"15 Months", 15,
"16 Months", 16,
"17 Months", 17,
"18 Months", 18)