Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Zack White 7Zack White 7 

Percent type formula field outputting "#error" using case function

The use case I'm working with has our customer accounts going through multiple stages notated by the Stage__c picklist field. Each stage can have multiple requirements stored as records in a related child object called Requirements.

My end goal is to have a formula field called "Current Stage Progression" which displays a percentage value based on the number of completed/validated requirements over the total number of requirements that must be completed before the Account's stage can progress.

I already have two separate number fields (for each Stage in the Account) using a custom lookup relationship Roll-up Summary piece to count the number of Validated or total Requirements in the child object and then store the values in the respective number fields (the "Count_of_Stage_X_VReqs__C" fields found in the formula below).

This Roll-up Counting + Number field piece is working and I've verified that it's delivering accurate values to the number fields, however my Formula to calculate the percentages is returning a "#Error!" value in the formula regardless of the number values it is supposedly working with. The result is the same regardless of how I treat zeroes (as zero or blank). In my test account it has two requirements for the stage and 1 validated requirement meaning the formula should display "50.00%" but instead returns the error.

The formula is like follows:
  
CASE(Stage__c,

"3",
 ( Count_of_Stage_3_VReqs__c /  Count_of_Stage_3_Reqs__c ),

"4",
 ( Count_of_Stage_4_VReqs__c /  Count_of_Stage_4_Reqs__c ),

"5",
 ( Count_of_Stage_5_VReqs__c /  Count_of_Stage_5_Reqs__c ),

"6",
 ( Count_of_Stage_6_VReqs__c /  Count_of_Stage_6_Reqs__c ),

"7",
 ( Count_of_Stage_7_VReqs__c /  Count_of_Stage_7_Reqs__c ),

"8",
 ( Count_of_Stage_8_VReqs__c /  Count_of_Stage_8_Reqs__c ),

"9",
 ( Count_of_Stage_9_VReqs__c /  Count_of_Stage_9_Reqs__c ),

"10",
 ( Count_of_Stage_10_VReqs__c /  Count_of_Stage_10_Reqs__c ),

"Service",
 ( Count_of_Service_Stage_VReqs__c /  Count_of_Service_Stage_Reqs__c )

,
0)
I expect the issue is a small detail I'm missing, but I'm missing it regardless. Any help would be appreciated.
Manish BhatiManish Bhati
According to this formula :-
Let us take first case
- It will check for Stage__c if its data type is text and value is 3.
- If true, it will return Count_of_Stage_3_VReqs__c /  Count_of_Stage_3_Reqs__c (Both data type should be same).
- If syntax or data type of any case is not correct it will throw an error.

Validate whether you are following the above, especially the Bold one.
Zack White 7Zack White 7
So I managed to get it working without errors by using multiple IF functions instead, but I'm still not sure why the CASE function wasn't working properly.

The IF function that works is as follows:
  
IF(ISPICKVAL(Stage__C , "3"),
  Count_of_Stage_3_VReqs__c / Count_of_Stage_3_Reqs__c ,

IF(ISPICKVAL(Stage__C , "4"),
  Count_of_Stage_4_VReqs__c /  Count_of_Stage_4_Reqs__c ,

IF(ISPICKVAL(Stage__C , "5"),
  Count_of_Stage_5_VReqs__c /  Count_of_Stage_5_Reqs__c ,

IF(ISPICKVAL(Stage__C , "6"),
  Count_of_Stage_6_VReqs__c /  Count_of_Stage_6_Reqs__c ,

IF(ISPICKVAL(Stage__C , "7"),
  Count_of_Stage_7_VReqs__c /  Count_of_Stage_7_Reqs__c ,

IF(ISPICKVAL(Stage__C , "8"),
  Count_of_Stage_8_VReqs__c /  Count_of_Stage_8_Reqs__c ,

IF(ISPICKVAL(Stage__C , "9"),
  Count_of_Stage_9_VReqs__c /  Count_of_Stage_9_Reqs__c ,

IF(ISPICKVAL(Stage__C , "10"),
  Count_of_Stage_10_VReqs__c /  Count_of_Stage_10_Reqs__c ,

IF(ISPICKVAL(Stage__C , "Service"),
  Count_of_Service_Stage_VReqs__c /  Count_of_Service_Stage_Reqs__c,
 
0
)))))))))


 
Deepak RathinaveluDeepak Rathinavelu
Hi Zack,

Can you try using case with the TEXT() function while using CASE, as the satge field is a picklist, it might be throwing the error.
  
CASE(TEXT(Stage__c),
//rest of the cases

hope it helps

Regards,
Deepak Rathinavelu
Deepak RathinaveluDeepak Rathinavelu
Hi Zack, 

For CASE() funtion the error shows up even if any one of the condition is ending up into an error, in your case its "division by zero",

Even in stage 3 satisfies the condition and a future stage say stage 9 field "Count_of_Stage_9_Reqs__c=0" its division by zero

Would end up in error.

All the best.

Deepak Rathinavelu