Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
Steve Cairney

# Scoring Formula IF(AND(OR Advice needed

Hi all, I'm writing a formula for quite a complex scoring matrix.

BACKGROUND: I have 8 picklists, the results of which hold a score number (4-1). One of the picklist values is NA and is counted separatley
These numbers are tallied WFR into two fields WFR_Score and WFR_NA.

If NA = 0 the results are

If the Score >=24, Excellent
>= 13 and < 23, Needs Improvement
<=12, Poor

Code is below. The problem is I can't get the Poor result to show as I think there is some conflict in the scoring less and greater than. Also I will have 8 iterations of this to do WRF_NA_Count__c=1, =2, =3 etc and I think my code is a little bloated. Can I streamline it down in line count?

```IF(
AND(
WFR_NA_Count__c = 0,
OR(
WFR_Score__c  >= 24)), "Excellent/Competent",
IF(
AND(
WFR_NA_Count__c = 0,
OR(
WFR_Score__c  >= 13,
WFR_Score__c  <= 23)), "Needs Improvement",
IF(
AND(
WFR_NA_Count__c = 0,
OR(
WFR_Score__c  < 12)), "Poor",
"ERROR"
)))```

Best Answer chosen by Steve Cairney
Saravana Muthu 8
Hi,

Please use the below formula for WFR_NA_Count__c from 1 to 8.

Data TypeFormula
IF(
AND(
WFR_NA_Count__c = 0,
OR(
WFR_Score__c >= 24)), "Excellent/Competent",
IF(
AND(
WFR_NA_Count__c = 0,
OR(
WFR_Score__c >= 13,
WFR_Score__c <= 23)), "Needs Improvement",
IF(
AND(
WFR_NA_Count__c = 0,
OR(
WFR_Score__c < 12)), "Poor",
IF(
AND(
WFR_NA_Count__c = 1,
OR(
WFR_Score__c >= 24)), "Excellent/Competent",
IF(
AND(
WFR_NA_Count__c = 1,
OR(
WFR_Score__c >= 13,
WFR_Score__c <= 23)), "Needs Improvement",
IF(
AND(
WFR_NA_Count__c = 1,
OR(
WFR_Score__c < 12)), "Poor",IF(
AND(
WFR_NA_Count__c = 2,
OR(
WFR_Score__c >= 24)), "Excellent/Competent",
IF(
AND(
WFR_NA_Count__c = 2,
OR(
WFR_Score__c >= 13,
WFR_Score__c <= 23)), "Needs Improvement",
IF(
AND(
WFR_NA_Count__c = 2,
OR(
WFR_Score__c < 12)), "Poor",IF(
AND(
WFR_NA_Count__c = 3,
OR(
WFR_Score__c >= 24)), "Excellent/Competent",
IF(
AND(
WFR_NA_Count__c = 3,
OR(
WFR_Score__c >= 13,
WFR_Score__c <= 23)), "Needs Improvement",
IF(
AND(
WFR_NA_Count__c = 3,
OR(
WFR_Score__c < 12)), "Poor",IF(
AND(
WFR_NA_Count__c = 4,
OR(
WFR_Score__c >= 24)), "Excellent/Competent",
IF(
AND(
WFR_NA_Count__c = 4,
OR(
WFR_Score__c >= 13,
WFR_Score__c <= 23)), "Needs Improvement",
IF(
AND(
WFR_NA_Count__c = 4,
OR(
WFR_Score__c < 12)), "Poor",IF(
AND(
WFR_NA_Count__c = 5,
OR(
WFR_Score__c >= 24)), "Excellent/Competent",
IF(
AND(
WFR_NA_Count__c = 5,
OR(
WFR_Score__c >= 13,
WFR_Score__c <= 23)), "Needs Improvement",
IF(
AND(
WFR_NA_Count__c = 5,
OR(
WFR_Score__c < 12)), "Poor",IF(
AND(
WFR_NA_Count__c = 6,
OR(
WFR_Score__c >= 24)), "Excellent/Competent",
IF(
AND(
WFR_NA_Count__c = 6,
OR(
WFR_Score__c >= 13,
WFR_Score__c <= 23)), "Needs Improvement",
IF(
AND(
WFR_NA_Count__c = 6,
OR(
WFR_Score__c < 12)), "Poor",IF(
AND(
WFR_NA_Count__c = 7,
OR(
WFR_Score__c >= 24)), "Excellent/Competent",
IF(
AND(
WFR_NA_Count__c = 7,
OR(
WFR_Score__c >= 13,
WFR_Score__c <= 23)), "Needs Improvement",
IF(
AND(
WFR_NA_Count__c = 7,
OR(
WFR_Score__c < 12)), "Poor",IF(
AND(
WFR_NA_Count__c = 8,
OR(
WFR_Score__c >= 24)), "Excellent/Competent",
IF(
AND(
WFR_NA_Count__c = 8,
OR(
WFR_Score__c >= 13,
WFR_Score__c <= 23)), "Needs Improvement",
IF(
AND(
WFR_NA_Count__c = 8,
OR(
WFR_Score__c < 12)), "Poor","Error"
)))))))))))))))))))))))))))

Please let me know if it helps.

Please don't forget to mark this as solved if it's resolved

Thanks,
Saravana.

Saravana Muthu 8
Hi,

Please use the below formula for WFR_NA_Count__c from 1 to 8.

Data TypeFormula
IF(
AND(
WFR_NA_Count__c = 0,
OR(
WFR_Score__c >= 24)), "Excellent/Competent",
IF(
AND(
WFR_NA_Count__c = 0,
OR(
WFR_Score__c >= 13,
WFR_Score__c <= 23)), "Needs Improvement",
IF(
AND(
WFR_NA_Count__c = 0,
OR(
WFR_Score__c < 12)), "Poor",
IF(
AND(
WFR_NA_Count__c = 1,
OR(
WFR_Score__c >= 24)), "Excellent/Competent",
IF(
AND(
WFR_NA_Count__c = 1,
OR(
WFR_Score__c >= 13,
WFR_Score__c <= 23)), "Needs Improvement",
IF(
AND(
WFR_NA_Count__c = 1,
OR(
WFR_Score__c < 12)), "Poor",IF(
AND(
WFR_NA_Count__c = 2,
OR(
WFR_Score__c >= 24)), "Excellent/Competent",
IF(
AND(
WFR_NA_Count__c = 2,
OR(
WFR_Score__c >= 13,
WFR_Score__c <= 23)), "Needs Improvement",
IF(
AND(
WFR_NA_Count__c = 2,
OR(
WFR_Score__c < 12)), "Poor",IF(
AND(
WFR_NA_Count__c = 3,
OR(
WFR_Score__c >= 24)), "Excellent/Competent",
IF(
AND(
WFR_NA_Count__c = 3,
OR(
WFR_Score__c >= 13,
WFR_Score__c <= 23)), "Needs Improvement",
IF(
AND(
WFR_NA_Count__c = 3,
OR(
WFR_Score__c < 12)), "Poor",IF(
AND(
WFR_NA_Count__c = 4,
OR(
WFR_Score__c >= 24)), "Excellent/Competent",
IF(
AND(
WFR_NA_Count__c = 4,
OR(
WFR_Score__c >= 13,
WFR_Score__c <= 23)), "Needs Improvement",
IF(
AND(
WFR_NA_Count__c = 4,
OR(
WFR_Score__c < 12)), "Poor",IF(
AND(
WFR_NA_Count__c = 5,
OR(
WFR_Score__c >= 24)), "Excellent/Competent",
IF(
AND(
WFR_NA_Count__c = 5,
OR(
WFR_Score__c >= 13,
WFR_Score__c <= 23)), "Needs Improvement",
IF(
AND(
WFR_NA_Count__c = 5,
OR(
WFR_Score__c < 12)), "Poor",IF(
AND(
WFR_NA_Count__c = 6,
OR(
WFR_Score__c >= 24)), "Excellent/Competent",
IF(
AND(
WFR_NA_Count__c = 6,
OR(
WFR_Score__c >= 13,
WFR_Score__c <= 23)), "Needs Improvement",
IF(
AND(
WFR_NA_Count__c = 6,
OR(
WFR_Score__c < 12)), "Poor",IF(
AND(
WFR_NA_Count__c = 7,
OR(
WFR_Score__c >= 24)), "Excellent/Competent",
IF(
AND(
WFR_NA_Count__c = 7,
OR(
WFR_Score__c >= 13,
WFR_Score__c <= 23)), "Needs Improvement",
IF(
AND(
WFR_NA_Count__c = 7,
OR(
WFR_Score__c < 12)), "Poor",IF(
AND(
WFR_NA_Count__c = 8,
OR(
WFR_Score__c >= 24)), "Excellent/Competent",
IF(
AND(
WFR_NA_Count__c = 8,
OR(
WFR_Score__c >= 13,
WFR_Score__c <= 23)), "Needs Improvement",
IF(
AND(
WFR_NA_Count__c = 8,
OR(
WFR_Score__c < 12)), "Poor","Error"
)))))))))))))))))))))))))))

Please let me know if it helps.

Please don't forget to mark this as solved if it's resolved

Thanks,
Saravana.
This was selected as the best answer
Steve Cairney
Hi that complies within the limits but I've noticed that the NA Counter isn't counting the N/A choice in the picklists, So I can't test correctly. Do you know why?

Heres the code:
```IF( ISPICKVAL( Picklist_1__c , 'Excellent') , 0,
IF(  ISPICKVAL( Picklist_1__c , 'Competent')  , 0,
IF( ISPICKVAL( Picklist_1__c , 'Needs Further Development') , 0,
IF( ISPICKVAL( Picklist_1__c , 'Poor') , 0,
IF( ISPICKVAL( Picklist_1__c , 'N/A') , 1,0
)))))
+
IF( ISPICKVAL( Picklist_2__c , 'Excellent') , 0,
IF(  ISPICKVAL( Picklist_2__c , 'Competent')  , 0,
IF( ISPICKVAL( Picklist_2__c , 'Needs Further Development') , 0,
IF( ISPICKVAL( Picklist_2__c , 'Poor') , 0,
IF( ISPICKVAL( Picklist_2__c , 'N/A') , 1,0
)))))
+
IF( ISPICKVAL( Picklist_3__c , 'Excellent') , 0,
IF(  ISPICKVAL( Picklist_3__c , 'Competent')  , 0,
IF( ISPICKVAL( Picklist_3__c , 'Needs Further Development') , 0,
IF( ISPICKVAL( Picklist_3__c , 'Poor') , 0,
IF( ISPICKVAL( Picklist_3__c , 'N/A') , 1,0
)))))```

Saravana Muthu 8
Sorry i can't able to understand what you are trying to say.