• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Period Earning Help, Adding different Metrics with different Goals

jcoloning

New Member
IF(OR(E2="CPH",E2="Critical Accuracy",E2="Quality"),LOOKUP(H2,{0,100,111,120},{4,3,2,1})
=IF(OR(E2={"CPH","Critical Accuracy","Quality"}),5-MATCH(H2,{0,1,1.11,1.2}),"not found")

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


TiersCPH (% to Goal)QualityTPRUtilizationCritical Accuracy
Tier 1120%100%110%81%100%
Tier 2110%97.10%100%73%99.60%
Tier 3100%99.10%90%70%99.10%
Tier 4<100%<99.1%<90%<70%<99.1%
 

jcoloning

New Member
I try to use this formula Merge, but it does not work.
=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,)))))

=IF(E2="Appeals CPH",IF(H2<104%,4,IF(AND(H2>=104%,H2<110%),3,IF(AND(H2>=109%,H2<115%),2,IF(AND(H2>=115%),1,)))))

=IF(E2="AHT",IF(H2<100%,4,IF(AND(H2>=100%,H2<125%),3,IF(AND(H2>=125%,H2<150%),2,IF(AND(H2>=150%),1,)))))

=IF(E2="TPR",IF(H2<90%,4,IF(AND(H2>=90%,H2<100%),3,IF(AND(H2>=100%,H2<110%),2,IF(AND(H2>=110%),1,)))))

=IF(E2="Utilization",IF(H2<70%,4,IF(AND(H2>=70%,H2<73%),3,IF(AND(H2>=73%,H2<81%),2,IF(AND(H2>=81%),1,)))))




=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,IF(E2="Appeals CPH",IF(H2<104%,4,IF(AND(H2>=104%,H2<110%),3,IF(AND(H2>=109%,H2<115%),2,IF(AND(H2>=115%),1,IF(E2="AHT",IF(H2<100%,4,IF(AND(H2>=100%,H2<125%),3,IF(AND(H2>=125%,H2<150%),2,IF(AND(H2>=150%),1,IF(E2="TPR",IF(H2<90%,4,IF(AND(H2>=90%,H2<100%),3,IF(AND(H2>=100%,H2<110%),2,IF(AND(H2>=110%),1,=IF(E2="Utilization",IF(H2<70%,4,IF(AND(H2>=70%,H2<73%),3,IF(AND(H2>=73%,H2<81%),2,IF(AND(H2>=81%),1,)))))
))))))))))))))))))))))))))))))
 
Top