Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Jaime Ortega 6Jaime Ortega 6 

Formula varies on picklist value and employee headcount

I'm trying to figure out a formula and need some help. The following is a formula I’m using that calculates a number for me. I’ll define each piece first
ACCI__c is a product
326 is a dollar amount
Number of Eligible EEs__c is an employee count
*0.20 gives me 20% of 326 x Employees
The rest is if there is no number.
 
IF ( ACCI__c , 326* (Number_of_Eligible_EEs__c*0.20) , 0* Number_of_Eligible_EEs__c )
 
So, here’s what I’m trying to do. First I have another picklist field I added called Target__c and the values are Target and Non-Target.
 
Example: If Eligible_EEs__c is between 100 and 200 and the picklist value from Target__c chosen is Target then the multiplier is 0.20, but if Eligible_EEs__c is 100 to 200 and the picklist value from Target__c is Non-Target the multiplier is 0.18
 
If Eligible_EEs__c is 201 to 400 and the picklist value from Target__c is Target then 0.16
If Eligible_EEs__c is 201 to 400 and picklist value from Target__c is Non-Target then 0.14
 
Would appreciate the correct formula to accomplish this. The head count variances and picklist values is where I’m stuck.
 
Nayana KNayana K
IF(ACCI__c != NULL, 
CASE(Target__c,
‘Target’, IF(AND(Number_of_Eligible_EEs__c >= 100, Number_of_Eligible_EEs__c <= 200),326* Number_of_Eligible_EEs__c*0.20,IF(AND(Number_of_Eligible_EEs__c >= 201, Number_of_Eligible_EEs__c <= 400),326* Number_of_Eligible_EEs__c*0.16,0)),
‘Non-Target’, IF(AND(Number_of_Eligible_EEs__c >= 100, Number_of_Eligible_EEs__c <= 200),326* Number_of_Eligible_EEs__c*0.18,IF(AND(Number_of_Eligible_EEs__c >= 201, Number_of_Eligible_EEs__c <= 400),326* Number_of_Eligible_EEs__c*0.14,0)),
0),
0)

OR
  
IF(ACCI__c != NULL, 
IF(AND(Number_of_Eligible_EEs__c >= 100, Number_of_Eligible_EEs__c <= 200),
CASE(Target__c,
‘Target’,326* Number_of_Eligible_EEs__c*0.20,
‘Non-Target’,326* Number_of_Eligible_EEs__c*0.18,
0
)
,
IF(AND(Number_of_Eligible_EEs__c >= 201, Number_of_Eligible_EEs__c <= 400),
CASE(Target__c,
‘Target’,326* Number_of_Eligible_EEs__c*0.16,
‘Non-Target’,326* Number_of_Eligible_EEs__c*0.14,
0
)
,0)
)
0)

Not sure whether this will exceed compile size. But you can give a try.