1

Any ideas on why this returns Missing ")"? Thanks!

CASE ( Mission_Aligned__c,
"No elements aligned",1,
"Few elements aligned",2,
"Some elements aligned",3,
"Most elements aligned",4,
"All elements aligned",5,

CASE (  Quality_Strength_Confidence_of_Program__c,
"Not research based",1,
"Some research based",2,
"Majority research based",3,
"Evidence informed",4,
"Evidence based",5,

CASE ( Participant_Impact__c,
"Low",1,
"Low to medium",2,
"Medium",3,
"Medium to high",4,
"High",5,

CASE ( Staff__c,
"None",1,
"People in training",2,
"Minimum required staff to deliver",3,
"Minimum extra staff on hand to deliver",4,
"Numerous staff on hand to deliver",5, 

CASE ( Funding_Availability__c,
"Within 5+ years",1,
"Within last 4 years",2,
"Within past 3 years",3,
"Within past 2 years",4,
"Within last year",5,

CASE ( Content_Updated__c,
"Within 5+ years",1,
"Within last 4 years",2,
"Within past 3 years",3,
"Within past 2 years",4,
"Within last year",5,

CASE ( Marketing_Materials_Developed_and_Utiliz__c,
"Within 5+ years",1,
"Within last 4 years",2,
"Within past 3 years",3,
"Within past 2 years",4,
"Within last year",5,

CASE (  Demand_in_languages_other_than_English__c,
"4 or more requests in last year",1,
"3 requests in last year",2,
"2 requests in last year",3,
"1 request in last year",4,
"No request in last year",5, 

CASE ( Relates_to_Community_Trends_Needs_Fund__c,
"No community stakeholder trend identification",1,
"1-2 community stakeholder trend identification"2,
"3-4 community stakeholder trend identification",3,
"4-5 community stakeholder trend identification",4,
"6+ community stakeholder trend identification",5,

CASE ( Family_Interest_and_Needs__c,
"29% or less people completed the class",1,
"30%-44% people completed the class",2,
"45%-59% people completed the class",3,
"60%-84% people completed the class",4,
"85% or more people completed the class",5,

CASE ( Supports_Partnerships__c,
"0 partnerships involved",1,
"1 partnership involved",2,
"2 partnerships involved",3,
"3 partnerships involved",4,
"4+ partnerships involved",5, 

NULL)))))))))))

Second draft formula, at this point returns syntax error Missing ":

( CASE ( Mission_Aligned__c,

"No elements aligned",0,

"Few elements aligned",1,

"Some elements aligned",2,

"Most elements aligned",3,

"All elements aligned",4,0)

+

CASE ( Quality_Strength__c,

"Not research based",0,

"Some research based",1,

"Majority research based",2,

"Evidence informed",3,

"Evidence based",4,0)

+

CASE ( Participant_Impact__c,

"Low",0,

"Low to medium",1,

"Medium",2,

"Medium to high",3,

"High",4,0)

+

CASE ( Staff_Capacity_to_Deliver__c,

"None",0,

"People in training",1,

"Minimum required staff to deliver",2,

"Minimum extra staff on hand to deliver",3,

"Numerous staff on hand to deliver”,4,0)

+

CASE ( Funding_Availability__c,

"Within 5+ years",0,

"Within last 4 years”,1,

"Within past 3 years”,2,

"Within past 2 years”,3,

"Within last year",4,0)

+

CASE ( Content_Updated__c,

"Within 5+ years",0,

"Within last 4 years”,1,

"Within past 3 years”,2,

"Within past 2 years”,3,

"Within last year",4,0)

+

CASE ( Funding_Recency_Implementation__c,

"Within 5+ years",0,

"Within last 4 years”,1,

"Within past 3 years”,2,

"Within past 2 years”,3,

"Within last year",4,0)

+

CASE ( Marketing_Materials_Developed_and_Utiliz__c,

"Within 5+ years",0,

"Within last 4 years”,1,

"Within past 3 years”,2,

"Within past 2 years”,3,

"Within last year",4,0)

+

CASE ( Demand_for_languages_other_than_English__c,

"4 or more requests in last year",0,

"3 requests in last year",1,

"2 requests in last year",2,

"1 request in last year",3,

"No request in last year",4,0)

+

CASE ( Relates_to_Community_Trends_Needs_Fund__c,

"No community stakeholder trend identification",0,

"1-2 community stakeholder trend identification”,1,

"3-4 community stakeholder trend identification”,2,

"4-5 community stakeholder trend identification”,3,

"6+ community stakeholder trend identification”,4,0)

  • CASE ( Family_Interest_and_Needs__c,

"29% or less people completed the class",0,

"30%-44% people completed the class”,1,

"45%-49% people completed the class”,2,

"60$-84% people completed the class”,3,

"85% or more people completed the class”,4,0)

+

CASE ( Supports_Partnerships__c,

"0 partnerships involved",0,

"1 partnership involved”,1,

"2 partnerships involved”,2,

"3 partnerships involved”,3,

"4+ partnerships involved”,4,0))

/

(IF (ISBLANK(TEXT( Mission_Aligned__c)),0,1)

+

IF (ISBLANK(TEXT ( Quality_Strength__c)),0,1)

+

IF (ISBLANK(TEXT ( Participant_Impact__c)),0,1)

+

IF (ISBLANK(TEXT ( Staff_Capacity_to_Deliver__c)),0,1)

+

IF (ISBLANK(TEXT ( Funding_Availability__c)),0,1)

+

IF (ISBLANK(TEXT ( Content_Updated__c)),0,1)

+

IF (ISBLANK(TEXT ( Funding_Recency_Implementation__c)),0,1)

+

IF (ISBLANK(TEXT ( Marketing_Materials_Developed_and_Utiliz__c)),0,1)

+

IF (ISBLANK(TEXT ( Demand_for_languages_other_than_English__c)),0,1)

+

IF (ISBLANK(TEXT ( Relates_to_Community_Trends_Needs_Fund__c)),0,1)

+

IF (ISBLANK(TEXT ( Family_Interest_and_Needs__c)),0,1)

+

IF (ISBLANK(TEXT ( Supports_Partnerships__c)),0,1))

9
  • No worries! Im curious if I could write it in a way so following picklist selection, the numeric values are added up and then divided by 11 (basically an average)? Aug 9, 2019 at 2:07
  • Sure - is it the case that all of the picklists are usually completed? Because what your current formula does is make a sort of hierarchy from them: If there's no score for Mission Alignment, then we look at Quality & Strength of Program, and so forth. Aug 9, 2019 at 3:09
  • To make the average, each picklist should have a complete CASE(), with a 0 option for no match, then add & divide: (CASE ( Mission_Aligned__c,"No elements aligned",1,"Few elements aligned",2,"Some elements aligned",3, "Most elements aligned",4,"All elements aligned",5,0) + CASE ( Quality_Strength_Confidence_of_Program__c,"Not research based",1, "Some research based",2,"Majority research based",3, "Evidence informed",4,"Evidence based",5,0) + [...9 other CASEs...] "4+ partnerships involved",5,0)) / 11 Aug 9, 2019 at 3:46
  • Thanks @Thomas, this makes sense. You brought up a great point on picklist completion. In the case that a field is left uncompleted and I don't want it to equal zero and want to subtract it from the denominator (ie if one field is not completed; divide by 10 rather than 11) any method to do this? Aug 9, 2019 at 4:03
  • You might run out of characters for formula length, but you could try making the denominator be IF(ISBLANK(TEXT(Picklist1__c)),0,1) + IF(ISBLANK(TEXT(Picklist2__c)),0,1) + ... Aug 9, 2019 at 4:08

1 Answer 1

2

You've got a missing comma lurking:

"1-2 community stakeholder trend identification"2,

I suspect the way the parser interprets that ends up unbalancing the paired parameters, making it believe your formula is intended to end early - hence looking for a close parenthesis.

1

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .