You need to sign in to do that
Don't have an account?
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?
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" )))
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
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.
Heres the code:
Can you please elobarate?
Thanks,
Saravana