ShowAll Questionssorted byDate Posted
GMASJ

# Formula Calculation

Hi

We have a requirement to write a formula field for calulating below details are mentioned as follows.

1. Unit of Measure ( Which is pick list holds Year/Month/Days)
2. List Price
3. Quantity
4. Subscription Term

Here Subscription Term is dependent on Unit of Measure
if Unit of Measure  = Year it will populate ( 1 to 5)
if Unit of Measure  = Month it will populate ( 1 to 12)
if Unit of Measure  = Days it will populate ( 1 to 31)

Based on the above value I need to build a formula field

IF Unit of Measure = Year = ( List Price * Year * Quantity )

IF Unit of Measure = Month = ( List Price / 12(Months in year)  * Quantity )

IF Unit of Measure = Days = ( List Price / 365(Days in year) * Quantity )

Please suggest me how to get this formula

Thanks

Sudhir

Best Answer chosen by GMASJ
shephali
Hi Sudhir Narayanaswamy,
Using the field depency yuo can easily get the following :
Here Subscription Term is dependent on Unit of Measure
if Unit of Measure  = Year it will populate ( 1 to 5)
if Unit of Measure  = Month it will populate ( 1 to 12)
if Unit of Measure  = Days it will populate ( 1 to 31) .
After that try the below formula to calculate :
```IF(ISPICKVAL(unit_of_measure__c, "years"),(list_price__c  *  VALUE(TEXT(subscription_term__c))*  quantity__c ), IF(ISPICKVAL(unit_of_measure__c, "months"),(list_price__c  / VALUE(TEXT(subscription_term__c))*quantity__c),
IF(ISPICKVAL(unit_of_measure__c, "days"),(list_price__c  / VALUE(TEXT(subscription_term__c))  *  quantity__c) , 00000.00) ) )```

Thanks
shephali

### All Answers

Bryan James
Hey Sudhir,
This should be able to be accomplished using a CASE in the advanced formula editor.
You can use this link to see how Salesforce likes to use them.
`https://help.salesforce.com/apex/HTViewHelpDoc?id=customize_functions_a_h.htm&language=en_US#CASE`

shephali
Hi Sudhir Narayanaswamy,
Using the field depency yuo can easily get the following :
Here Subscription Term is dependent on Unit of Measure
if Unit of Measure  = Year it will populate ( 1 to 5)
if Unit of Measure  = Month it will populate ( 1 to 12)
if Unit of Measure  = Days it will populate ( 1 to 31) .
After that try the below formula to calculate :
```IF(ISPICKVAL(unit_of_measure__c, "years"),(list_price__c  *  VALUE(TEXT(subscription_term__c))*  quantity__c ), IF(ISPICKVAL(unit_of_measure__c, "months"),(list_price__c  / VALUE(TEXT(subscription_term__c))*quantity__c),
IF(ISPICKVAL(unit_of_measure__c, "days"),(list_price__c  / VALUE(TEXT(subscription_term__c))  *  quantity__c) , 00000.00) ) )```

Thanks
shephali
This was selected as the best answer
shephali
Please dont forget to mark it as solution if you get your answer.
GMASJ
Thanks Shephali for you reply Just want to know I tried below usign case does his have any issue let me know.
```CASE(1,
IF(ISPICKVAL (cldy_uom__c, "Year(s)"),1,0),
(( UnitPrice * Discount ) * VALUE(Subscription_Term_Formula__c) *  Quantity ),
IF(ISPICKVAL (cldy_uom__c, "Month(s)"),1,0),
(( (UnitPrice / 12) * Discount ) * VALUE(Subscription_Term_Formula__c) *  Quantity ),
IF(ISPICKVAL (cldy_uom__c, "Day(s)"),1,0),
(( (UnitPrice / 365) * Discount ) * VALUE(Subscription_Term_Formula__c) *  Quantity ),
0
)```

Thanks
Sudhir
shephali
It wont work as picklist value must be first converted to text .