 ShowAll Questionssorted byDate Posted Jaime 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 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.