function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ 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) 
Steve :-/Steve :-/

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
Steve :-/Steve :-/
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


Steve :-/Steve :-/
No problem, you owe me a beer ;-)
 
http://www.russianriverbrewing.com/web/brews/plinytheelder.htm
 


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


Steve :-/Steve :-/
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)
 


Steve :-/Steve :-/
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


Steve :-/Steve :-/
 
Is it supposed to be 
 
(Total_NRC__c   +  Total_MRC__c)   x  12
 
OR
 
Total_NRC__c   +  (Total_MRC__c   x 12) 
 


Steve :-/Steve :-/
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....