+ Start a Discussion
TcynTcyn 

IF / Case Statement Logical Function - Basic Help

I have a 'Closure_Probability_c' pick list with three variables 'High', 'Medium' 'Low'. I created a custom field 'opportunity_weight_c'. I am having difficulty progressing the first part of the formula:

 

IF(CONTAINS(closure_probability_c, "Low"), .03)  *  [ (HW_c) + (SW_c *12) ] = opportunity_weight_c

 

Using the IF logical function, is it possible to replace 'High' or 'Medium' or 'Low' with an assigned weighted numerical value .95, .07, .03 respectivly AND multiply by the sum of HW and SW cost? Is it possible to string the statements together for each High, Medium, Low variable on one line and if so what will the forumula look like? I am able to do this in Excel using VLOOKUP, but I am new to SalesForce. Thanks in advance.

 

 

 

Best Answer chosen by Admin (Salesforce Developers) 
SteveMo__cSteveMo__c

Are you looking for something like this?

 

 

CASE(closure_probability_c,

"Low", 0.03 * (HW_c + (SW_c *12)),

"Medium", 0.07 * (HW_c + (SW_c *12)),

"High", 0.95 * (HW_c + (SW_c *12)),

NULL)

 

 

 

Message Edited by Stevemo on 02-10-2010 10:37 PM

All Answers

SteveMo__cSteveMo__c

Are you looking for something like this?

 

 

CASE(closure_probability_c,

"Low", 0.03 * (HW_c + (SW_c *12)),

"Medium", 0.07 * (HW_c + (SW_c *12)),

"High", 0.95 * (HW_c + (SW_c *12)),

NULL)

 

 

 

Message Edited by Stevemo on 02-10-2010 10:37 PM
This was selected as the best answer
TcynTcyn
Thanks. That looks like it albeit cleaner than what I have on my notepad. I'll give that a shot and report back. Thanks again :)
Message Edited by Tcyn on 02-10-2010 08:53 PM
SteveMo__cSteveMo__c
TcynTcyn

Open your mind to this...

 

http://www.stonebrew.com/ruin/

 

though you will have to wait. The almight check syntax box returns Error. Syntax error:

 

Extra ',' and Extra 'Medium' and I suppose it would continue with the two other pick window variables. I'll continue to hack away.

 

Message Edited by Tcyn on 02-10-2010 09:37 PM
SteveMo__cSteveMo__c

I've had Ruination a few times (tasty stuff) I was able to track down Pliny when I was in SF for Dreamforce last year.  

 

Can you post your formula using the Code Clipboard?  

 

My formula is written to evaluate a Picklist Field and  return a numeric value which is then inserted into the rest of the formula.  

 

Basically it is saying

 

IF Picklist = Low THEN 0.03

IF Picklist = Medium THEN 0.07 

IF Picklist = High THEN 0.95

 

THEN take those results and use them in this formula

 

Result from Picklist  * (HW__c + (SW__c *12))

 

Is that close to what you are looking for? 

 

I'm also going under these assumptions:

 

 

  • closure_probability_c is a single-value picklist with 3 options (Low, Medium, High) 
  • HW__c is a Currency Field
  • SW__c is a Currency Field
  • This formula is going into a Formula(Currency) field

 


Message Edited by Stevemo on 02-11-2010 01:34 AM
TcynTcyn
I made some changes to the field names and what you posted is exactly what I want to do. The problem is that I'm a bit challenged on the logical formula syntax. Thanks for all your help thus far.

CASE( Closure_Probability__c ,
"Low", 0.03 * ( Total_NRC__c + Total_MRC__c * 12)) ,
"Medium", 0.07 * ( Total_NRC__c + Total_MRC__c * 12)) ,
"High", 0.95 * ( Total_NRC__c + Total_MRC__c * 12)) ,
NULL)

 

SteveMo__cSteveMo__c

Okay, right off the bat I notice that your formula 

 

 

CASE( Closure_Probability__c ,

"Low", 0.03 * ( Total_NRC__c + Total_MRC__c * 12)) ,

"Medium", 0.07 * ( Total_NRC__c + Total_MRC__c * 12)) ,

"High", 0.95 * ( Total_NRC__c + Total_MRC__c * 12)) ,

NULL)

 

 uses a slightly different calculation than mine

 

 

 

CASE(closure_probability_c,

"Low", 0.03 * (Total_NRC__c + (Total_MRC__c *12)),

"Medium", 0.07 * (Total_NRC__c + (Total_MRC__c *12)),

"High", 0.95 * (Total_NRC__c + (Total_MRC__c *12)),

NULL)

 

So your calculation is: 

closure_probability_c * ( Total_NRC__c + Total_MRC__c * 12))

 

 

 and mine is: 

 

closure_probability_c * ( Total_NRC__c + (Total_MRC__c * 12))

 

 PS.   I'm gettin' thirsty...
Message Edited by Stevemo on 02-11-2010 01:41 AM
SteveMo__cSteveMo__c

 

Is it supposed to be 

 

(Total_NRC__c   +  Total_MRC__c)   x  12

 

OR

 

Total_NRC__c   +  (Total_MRC__c   x 12) 

 

SteveMo__cSteveMo__c

2:00 am Boston time, packing it in...

 

Good Luck! 

TcynTcyn

It is supposed to be the later (Total_NRC__c   +  (Total_MRC__c   x 12) ). But alas, now I am moving. I corrected the missing parenthesis and found and corrected an incorrect underscore character (too narrow) and got the following error:

 

Error: Field Closure_Probability__c is a picklist field. Picklist fields are only supported in certain functions.

 

And it looks like the field supports ISPICKVAL, CASE and TEXT. Hmmmm, I'm thinking the pick list value will need to be converted into a text value to search for 'High', 'Medium' and 'Low' and replace with their respective value (.95, .07, .03)? In the mean time, I continue to hack away. Thanks!

 

Message Edited by Tcyn on 02-11-2010 12:14 AM
TcynTcyn

Success! Thanks for your help. I was missing the initial CASE statement in addition to the missing parenthesis and wrong underscore character. Once those were addressed, I layered the lines one by one making sure the calculations were valid. Thanks again. Where do you want that Pliny the Elder sent  :smileyhappy:

 

It's 1:00 am California time - I'm out. Thanks again.

 

 

CASE(Closure_Probability__c ,

"Low", 0.03 * (Total_NRC__c + (Total_MRC__c *12)),
"Medium", 0.07 * (Total_NRC__c + (Total_MRC__c *12)),
"High", 0.95 * (Total_NRC__c + (Total_MRC__c *12)),
NULL)

 

 

 

 

 

Message Edited by Tcyn on 02-11-2010 12:55 AM
Message Edited by Tcyn on 02-11-2010 12:56 AM
Stuart Edeal.Stuart Edeal.
AND - now I owe Steve Molis yet another beer - thank you very much....