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))

|improve this question
  • 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)? – Milad Mohammadi Aug 9 '19 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. – Thomas Taylor Aug 9 '19 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 – Thomas Taylor Aug 9 '19 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? – Milad Mohammadi Aug 9 '19 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) + ... – Thomas Taylor Aug 9 '19 at 4:08
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.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.