function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Steve CairneySteve 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 8Saravana 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 change the output result text accrding to your need.

Please let me know if it helps.

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

Thanks,
Saravana.

All Answers

Saravana Muthu 8Saravana 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 change the output result text accrding to your need.

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 CairneySteve 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 8Saravana Muthu 8
Sorry i can't able to understand what you are trying to say.

Can you please elobarate?

Thanks,
Saravana
Steve CairneySteve Cairney
Sorry, I had to change "N/A" to "NA". SF must not like / in it so it was throwing up a zero. This works perfectly now. Thanks