Period Earning Help!!!!

[jcoloning]

Good morning,


Hello, I;m working in a compensation program for my company and I'm using metrics and hours worked. We have Tiers to identify if the employee meet or not meet the Quality and Productivity.
Tier 1=$400
Tier 2 =$200
Tier 3 = $100
Tier 4 = $0.00

Base on Hours Worked, we had define a Prorate Factor. Were the Goal in worked hours is 134.
<50, =0%
=>50 / 134 = Result in %

and I'm trying to calculated the period Pre-earnings, based on Tiers and hours worked.

Hours Worked divide in Hours Goal equal to prorate Factor multiply by Tier = Pre-earning period

For example: if a employee had worked 160 hours a month, and in Quality the Tier is 2 and in Productivity the Tier is 1.

I need to calculate
Hours Worked= 160
Prorate Factor is 100%
Quality Tier 2= $200
Productivity Tier 1: $400

Calculation would be:

The pre-erning Period is $600


Other Example.
Hours Worked= 100
Prorate Factor is 76%
Quality Tier 2= $200
But, does not meet hours worked so i need to multply the Tier 2= $200, by Prorate factor that is 76% the pre-earning period would be $152.00
Productivity Tier 3: $100
But, does not meet hours worked so i need to multply the Tier 3= $100, by Prorate factor that is 76% the pre-earning period would be $$76.00

I need a formula that only calculate the Period Pre-Earning. in $

Because I used =IF(j2>=134,k2*400),If(j2<=133,k2*200),If(j2<=50,k2*100) but the result am getting is True or false.

 

[jcoloning]

I have the formula but i dont know how to merge the three formula in one.

=IF(E2="CPH",IF(H2<100%,4,IF(AND(H2>=100%,H2<=110%),3,IF(AND(H2>=111%,H2<=119%),2,IF(AND(H2>=120%),1,)))))
=IF(E2="Critical Accuracy",IF(H2<100%,4,IF(AND(H2>=100%,H2<=110%),3,IF(AND(H2>=111%,H2<=119%),2,IF(AND(H2>=120%),1,)))))
=IF(E2="Quality",IF(H2<100%,4,IF(AND(H2>=100%,H2<=110%),3,IF(AND(H2>=111%,H2<=119%),2,IF(AND(H2>=120%),1,)))))

 

[pecoflyer]

Perhaps =IF(OR(E2="CPH",E2="Critical Accuracy",E2="Quality"),LOOKUP(H2,{0,100,111,120},{4,3,2,1})

 

[bosco_yip]

Or................

=IF(OR(E2={"CPH","Critical Accuracy","Quality"}),5-MATCH(H2,{0,1,1.11,1.2}),"not found")

Regards

 

[pecoflyer]

Bosco, which should be the syntax of your arrays using non-English regional settings ?
Replacing commas with semi-colons does not work ?
I tried the backslash to no avail

 

[bosco_yip]

Bosco, which should be the syntax of your arrays using non-English regional settings ?
Replacing commas with semi-colons does not work ?
I tried the backslash to no avail

I am using English version Excel 2019.

Here's is a file and formula comparison table as per following

1] Bosco's formula :

=IF(OR(E2={"CPH","Critical Accuracy","Quality"}),5-MATCH(H2,{0,1,1.11,1.2}),"not found")

2] pecoflyer's formula :

=IF(OR(E2="CPH",E2="Critical Accuracy",E2="Quality"),LOOKUP(H2,{0,1,1.11,1.2},{4,3,2,1}))

3] jcoloning's formula :

=IF(OR(E2={"CPH","Critical Accuracy","Quality"}),IF(H2<100%,4,IF(AND(H2>=100%,H2<=110%),3,IF(AND(H2>=111%,H2<=119%),2,IF(AND(H2>=120%),1,)))))



Remark :

I guess your problem maybe in this part formula >> " OR(E2={"CPH","Critical Accuracy","Quality"}) "

In the old Excel version we used >> " OR(E2="CPH",E2="Critical Accuracy",E2="Quality") "

but, I don't know when Excel started to allow using >> " OR(E2={"CPH","Critical Accuracy","Quality"}) "

Regards
Bosco

 

[jcoloning]

Or................

=IF(OR(E2={"CPH","Critical Accuracy","Quality"}),5-MATCH(H2,{0,1,1.11,1.2}),"not found")

Regards

Both work thanks, but i Forgot to include the goal for the metrics.

Tiers	CPH (% to Goal)	Quality	TPR	Utilization	Critical Accuracy
Tier 1	120%	100%	110%	81%	100%
Tier 2	110%	97.10%	100%	73%	99.60%
Tier 3	100%	99.10%	90%	70%	99.10%
Tier 4	<100%	<99.1%	<90%	<70%	<99.1%

 

[bosco_yip]

Both work thanks, but i Forgot to include the goal for the metrics.

Please open a new thread for your new question

Regards

 

[jcoloning]

Please open a new thread for your new question

Regards

Thread created. Thanks in advance!