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" )))

Saravana Muthu 8Hi,

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 8Hi,

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.

Steve CairneyHi 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:

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

Can you please elobarate?

Thanks,

Saravana

Steve CairneySorry, 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