ShowAll Questionssorted byDate Posted
northcountrygal

# Formula to calculate score based on two picklist fields

I have 2 picklists fields and one formula field for scoring.  I've modified the field names for sake of confidentiality . . . but the desired functionality is the same.

Picklist one is EEO Gender with the picklist values Male, Female
Picklist two is a dependent picklist showing Number of Push-ups

So say if we have Female in Picklist One and the option to choose 10, 15, 20, 25.
If we have Male in Picklist One the the option to choose 15, 20, 25, 30.

And depending on the number of push-ups a score is given.

So if a Female does 10 push-ups, she gets a score of 1, for 15 she is scored 2, for 20 a score of 3 and for 25 a score of 4.
But for the Male the scoring is different.  For 15 it's a score of 1, for 20 a score of 2, for 25 a score of 3 and for 30 a score of 4.

Is there a way to create a formula to calculate the correct score for either gender?  Can I modify the below to accomodate scoring for both?

Right now I have:

CASE(Pushup_list__c,
"<10", 1,
"15", 2,
"20", 3,
"25", 4,
0)

Arpit Jain7
Hello,

Your formula will look like below

if( ISPICKVAL( EEO_Gender__c ,"Female"),CASE( Pushup_list__c,
"<10", 1,
"15", 2,
"20", 3,
"25", 4,
0),If(ISPICKVAL( EEO_Gender__c ,"Male"),CASE( Pushup_list__c,
"15", 1,
"20", 2,
"25", 3,
"30", 4,
0),0))

Change API name as per your fields API names.

Hope this will help!!

Thanks
Arpit

Arpit Jain7
Small correction to above posted formula

if( ISPICKVAL( EEO_Gender__c ,"Female"),CASE( Pushup_list__c,
"10", 1,
"15", 2,
"20", 3,
"25", 4,
0),If(ISPICKVAL( EEO_Gender__c ,"Male"),CASE( Pushup_list__c,
"15", 1,
"20", 2,
"25", 3,
"30", 4,
0),0))

Change API name as per your fields API names.

Hope this will help!!

Thanks
Arpit