Draft Formula Syntax Error

Question

The purpose of this formula is to assign numeric values to some picklists and average them. This is a second draft which 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))

Answer 1

Unfortunately, you will hit SF Formula Field Limits and Restrictions with that formula, it's simply too big.

If you do not want to go for APEX, you can use two (or even more) formula fields and split your logic and an additional field, that holds the result, e.g.:

1. numeric values to some pick-lists (top of your formula)--> in one formula field "A"
2. If Statements (bottom of your formula) --> in a different formula field "B"
3. Additionally, you need a workflow rule and a field update to calculate the average "A" / "B". the result needs to be stored in a new result field.

This WF rule should fire hitting following criteria:

OR(
    ISCHANGED(Formula_A__c),
    ISCHANGED(Formula_B__c)
    )

Field update should be something like this

    Formula_A__c / Formula_B__c

Be careful that you do not divide by zero.

Hope, this is helpful! Best, Stefan

Answer 2

Looks like you're missing a + sign:

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

Answer 3

The problem here seems to be due to some "smart" quotes that worked their way into your formula.

Example:
"Within past 2 years”

The normal double-quote character, the one Salesforce is looking for to both start and end string literals, is ". It's part of the ASCII character set, hex 22

The "smart" end quote ” is found somewhere else in the UTF-8 encoding, and the code point is hex 201D

Replacing all of the smart quotes with plain double quotes should get you over this error.