Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Kim MoutesKim Moutes 

Excluding blank values from a formula

Hello-
I have a formula field that is calculating an "Opportunity Score" based on certain values. It is displaying accurate percentages EXCEPT when a field if blank. Blanks seem to hold the same value as Not Defined (0%). How do I exclude blank values into the below formula?

IF( 
ISPICKVAL(Opportunity_Timeframe__c, ""), 
(CASE(Text(Opportunity_Need__c),'Defined',100,'Partially Defined',50,'Not Defined',0,0) 
+CASE(Text(Opportunity_Expectation__c),'Defined',100,'Partially Defined',50,'Not Defined',0,0) 
)/200, 
IF( 
ISPICKVAL(Opportunity_Timeframe__c, "Introduction"), 
(CASE(Text(Opportunity_Need__c),'Defined',100,'Partially Defined',50,'Not Defined',0,0) 
+CASE(Text(Opportunity_Expectation__c),'Defined',100,'Partially Defined',50,'Not Defined',0,0) 
)/200, 
(CASE(Text(Opportunity_Need__c),'Defined',100,'Partially Defined',50,'Not Defined',0,0) 
+CASE(Text(Opportunity_Timeframe__c),'Defined',100,'Partially Defined',50,'Not Defined',0,0) 
+CASE(Text(Opportunity_Expectation__c),'Defined',100,'Partially Defined',50,'Not Defined',0,0) 
)/300))

In the example below, since Opportunity Expectation is set to Defined which is worth 100 and the other two fields are blank we want to give this opportunity a 100% score. 

User-added image

Any help would be much appreciated! Thank you.
Kim
Raj VakatiRaj Vakati
Modify as shown below  
IF(NOT(ISBLANK(Opportunity_Need__c)) , 
(CASE(Text(Opportunity_Need__c),'Defined',100,'Partially Defined',50,'Not Defined',0,0) ,' Value is NULL '  )

 
Kim MoutesKim Moutes
Raj,
Thank you for the quick response. Would I need to do this for each line? Like below.

IF( 
ISPICKVAL(Opportunity_Timeframe__c, ""), 
IF(NOT(ISBLANK(Opportunity_Need__c)),
(CASE(Text(Opportunity_Need__c),'Defined',100,'Partially Defined',50,'Not Defined',0,0),’Value is NULL’)
IF(NOT(ISBLANK(Opportunity_Expectation__c)),
+CASE(Text(Opportunity_Expectation__c),'Defined',100,'Partially Defined',50,'Not Defined',0,0),’Value is NULL’)
)/200, 
IF( 
ISPICKVAL(Opportunity_Timeframe__c, "Introduction"), 
IF(NOT(ISBLANK(Opportunity_Need__c)),
(CASE(Text(Opportunity_Need__c),'Defined',100,'Partially Defined',50,'Not Defined',0,0),’Value is NULL’)
IF(NOT(ISBLANK(Opportunity_Expectation__c)),
+CASE(Text(Opportunity_Expectation__c),'Defined',100,'Partially Defined',50,'Not Defined',0,0),’Value is NULL’)
)/200,
IF(NOT(ISBLANK(Opportunity_Need__c)),
(CASE(Text(Opportunity_Need__c),'Defined',100,'Partially Defined',50,'Not Defined',0,0),’Value is NULL’)
IF(NOT(ISBLANK(Opportunity_Timeframe__c)),
+CASE(Text(Opportunity_Timeframe__c),'Defined',100,'Partially Defined',50,'Not Defined',0,0) ,’Value is NULL’)
IF(NOT(ISBLANK(Opportunity_Expectation__c)),
+CASE(Text(Opportunity_Expectation__c),'Defined',100,'Partially Defined',50,'Not Defined',0,0) ,’Value is NULL’)
)/300))
 
Raj VakatiRaj Vakati
Yes .. 
Kim MoutesKim Moutes
I am receiving the below syntax error. 

User-added image
Kim MoutesKim Moutes
Raj- Thank you for your help. I ended up going with the below formula and it worked perfectly. The "else" statement at the end of each CASE function needed to be changed from 0 to 100. I also added an additional IF statement at the beginning so that if all 3 fields were blank it would display the score as 0.00%. Checked several scenarios to confirm that it was displaying accurate percentages. 

IF(
AND(
ISPICKVAL(Opportunity_Timeframe__c, ""),
ISPICKVAL(Opportunity_Need__c, ""),
ISPICKVAL(Opportunity_Expectation__c, "")), 0.00,
IF( 
ISPICKVAL(Opportunity_Timeframe__c, ""), 
(CASE(Text(Opportunity_Need__c),'Defined',100,'Partially Defined',50,'Not Defined',0,100) 
+CASE(Text(Opportunity_Expectation__c),'Defined',100,'Partially Defined',50,'Not Defined',0,100) 
)/200, 
IF( 
ISPICKVAL(Opportunity_Timeframe__c, "Introduction"), 
(CASE(Text(Opportunity_Need__c),'Defined',100,'Partially Defined',50,'Not Defined',0,100) 
+CASE(Text(Opportunity_Expectation__c),'Defined',100,'Partially Defined',50,'Not Defined',0,100) 
)/200, 
(CASE(Text(Opportunity_Need__c),'Defined',100,'Partially Defined',50,'Not Defined',0,100) 
+CASE(Text(Opportunity_Timeframe__c),'Defined',100,'Partially Defined',50,'Not Defined',0,100) 
+CASE(Text(Opportunity_Expectation__c),'Defined',100,'Partially Defined',50,'Not Defined',0,100) 
)/300)))