Hello! I'm new to Salesforce and I'm in the process of getting familiar with formulas. Our R&D team wants to create a R&D Score, based off of the Volume Priority (Text-Based Formula) and the Project Priority (Pick List). When I enter it and check syntax, it keeps telling me that there are extra commas, so I'll remove the commas. Then it tells me the numbers I want as my result are extra, etc. I'm not sure what I'm doing wrong. I have provided the formula below.
Thanks in advance!
IF( Volume_Priority__c, "1") && ISPICKVAL (project_priority__c, "1"), 100,
IF( Volume_Priority__c, "1") && ISPICKVAL (project_priority__c, "2"), 96,
IF( Volume_Priority__c "1") && ISPICKVAL (project_priority__c, "3"), 92,
IF( Volume_Priority__c, "1") && ISPICKVAL (project_priority__c, "4"), 88,
IF( Volume_Priority__c, "1") && ISPICKVAL (project_priority__c, "5"), 84,
IF( Volume_Priority__c, "2") && ISPICKVAL (project_priority__c, "1"), 80,
IF( Volume_Priority__c, "2") && ISPICKVAL (project_priority__c, "2"), 76,
IF( Volume_Priority__c, "2") && ISPICKVAL (project_priority__c, "3"), 72,
IF( Volume_Priority__c, "2") && ISPICKVAL (project_priority__c, "4"), 68,
IF( Volume_Priority__c, "2") && ISPICKVAL (project_priority__c, "5"), 64,
IF( Volume_Priority__c, "3") && ISPICKVAL (project_priority__c, "1"), 60,
IF( Volume_Priority__c, "3") && ISPICKVAL (project_priority__c, "2"), 56,
IF( Volume_Priority__c, "3") && ISPICKVAL (project_priority__c, "3"), 52,
IF( Volume_Priority__c, "3") && ISPICKVAL (project_priority__c, "4"), 48,
IF( Volume_Priority__c, "3") && ISPICKVAL (project_priority__c, "5"), 44,
IF( Volume_Priority__c, "4") && ISPICKVAL (project_priority__c, "1"), 40,
IF( Volume_Priority__c, "4") && ISPICKVAL (project_priority__c, "2"), 36,
IF( Volume_Priority__c, "4") && ISPICKVAL (project_priority__c, "3"), 32,
IF( Volume_Priority__c, "4") && ISPICKVAL (project_priority__c, "4"), 28,
IF( Volume_Priority__c, "4") && ISPICKVAL (project_priority__c, "5"), 24,
IF( Volume_Priority__c, "5") && ISPICKVAL (project_priority__c, "1"), 20,
IF( Volume_Priority__c, "5") && ISPICKVAL (project_priority__c, "2"), 16,
IF( Volume_Priority__c, "5") && ISPICKVAL (project_priority__c, "3"), 12,
IF( Volume_Priority__c, "5") && ISPICKVAL (project_priority__c, "4"), 8,
IF( Volume_Priority__c, "5") && ISPICKVAL (project_priority__c, "5"), 4, null))))))))))))))))))))))))))
This also works (using Case) and reduces the formula to 2,309 characters:
IF( Volume_Priority__c = "1", CASE(Project_Priority__c, "1", 100, "2", 96,"3", 92, "4", 88, "5", 84, null),
IF( Volume_Priority__c = "2", CASE(Project_Priority__c, "1", 80, "2", 76,"3", 72, "4", 68, "5", 64, null),
IF( Volume_Priority__c = "3", CASE(Project_Priority__c, "1", 60, "2", 56,"3", 52, "4", 48, "5", 44, null),
IF( Volume_Priority__c = "4", CASE(Project_Priority__c, "1", 40, "2", 36,"3", 32, "4", 28, "5", 24, null),
IF( Volume_Priority__c = "5", CASE(Project_Priority__c, "1", 20, "2", 16,"3", 12, "4", 8, "5", 4, 0), null)))))
And this uses math (since there's a pattern to the scoring here) and reduces the formula to 298 characters:
100 -
(
((VALUE(Volume_Priority__c)-1)*20) + ((VALUE(TEXT(Project_Priority__c))-1)*4)
)
And I should have the video up soon as well if you want to see how we troubleshot this!
