Need Formula to calculate KPI score

[SatishKumar.Dx]

KPI Achievement KPI SCORE SLA SCORE SLA SCORE/PERCENTAGE ACHIEVED
Outstanding 130% 100% 100.00%
Excellent 115% 95.0% - 99.99% what is the value KPI score for 98%?
Achieved 100% 90%-94.49% what is the value KPI score for 93.5%?
Under achieved 80% 80.00%-89.99% what is the value KPI score for 85%?
0% less than 80% what is the value KPI score for 72%?


Example:1 suppose if I have a SLA score of 94.7 then my KPI score is 115% as it falls Excellent
Example:2 suppose if I have a SLA score of 91.5 then my KPI score is 102% as it falls Achieved

 

[Atroxell]

More information is needed. A sample data set would be helpful.

How do you get from an SLA score of 94.7 to a KPI value of 115%? What formula are you currently using to result in the final % you list in your posting?

Even without those answers you can establish a table of % ranges and descriptions and perform a VLOOKUP() on that table with the score.

HTH

 

[SatishKumar.Dx]

Hello thanks a lot for your response,

Let me answer your first question.
1)How do you get from an SLA score of 94.7 to a KPI value of 115%?
Answer: I have been given with fixed values of KPI Score max of 130% if the SLA Score is 100%
If the SLA Score falls bwteen 95% to 99.99% then the KPI score falls between 115% to 130%.
I was doing this manually now i'm looking for a formula to automate it.

Procedure to calcuate SLA Score is:
Suppose u hav 20 computer if 1 computer fails to work then (1/20)=5%.
Hence out of 100% SLA we have lost 5% due to failure of 1 computer so the SLA Score would be (100%-5%)=95%.

This SLA Score of 95% falls in KPI Score table of 115%, Hence we have achived the KPI score of 115%.

Please find the KPI score Table below:
KPI SCORE
130%
115%
100%
80%
0%

Here bloew is my SLA Range for KPI Score:
SLA SCORE
100%
95.0% - 99.99%
90%-94.49%
80.00%-89.99%
less than 80%

Thank you once a again for your help.

Regards,
SK

 

[shg]

I think ...

=PERCENTILE({0,80,100,115,130}%, PERCENTRANK({0,80,90,95,100}%, A1))

... were A1 contains an SLA value.

       -A-- -B--
   1   SLA  KPI 
   2     0%   0%
   3    50%  50%
   4    80%  80%
   5    81%  82%
   6    82%  84%
   7    83%  86%
   8    84%  88%
   9    85%  90%
  10    86%  92%
  11    87%  94%
  12    88%  96%
  13    89%  98%
  14    90% 100%
  15    91% 103%
  16    92% 106%
  17    93% 109%
  18    94% 112%
  19    95% 115%
  20    96% 118%
  21    97% 121%
  22    98% 124%
  23    99% 127%
  24   100% 130%

 

[SatishKumar.Dx]

Hello Dear Friend Thanks a Million x Million,

You are really superb, the answer is perfect.

I don't have any words to express.

I would trouble you if I need any help in future you are my Excel Master from today.

Thank you.
SK

 

[SatishKumar.Dx]

Hi friends,

This is another scenario, can you help me again to find out a formula for this too.

KPI SCORE
130%
115%
100%
80%
0%

SLA SCORE
5%- 10%
10.1% - 15%
15.1% - 20%
20.1% - 25%
>25.1%


Regards,
SK

 

[shg]

Try changing the numbers in the formula.

 

[SatishKumar.Dx]

I tried doing it i get KPI of 0% for SLA of 5%, where I should get the KPI value between 115% to 130%.

I changed the value inside the formula as:
=PERCENTILE({0,80,100,115,130}%, PERCENTRANK({25,20.1,15.1,10.1,5}%, A1))


Could you please send me the formula if you have it ready, mean time I will try here.

Thanks,
SK

 

[shg]

       -A- -B-- C -D- -E-- ---------------------------------F---------------------------------
   1   SLA KPI    SLA KPI                                                                     
   2    5% 130%    5% 130% E2 and down: =PERCENTILE($B$2:$B$6, 1 - PERCENTRANK($A$2:$A$6, D2))
   3   10% 115%    6% 127%                                                                    
   4   15% 100%    7% 124%                                                                    
   5   20%  80%    8% 121%                                                                    
   6   25%   0%    9% 118%                                                                    
   7              10% 115%                                                                    
   8              11% 112%                                                                    
   9              12% 109%                                                                    
  10              13% 106%                                                                    
  11              14% 103%                                                                    
  12              15% 100%                                                                    
  13              16%  96%                                                                    
  14              17%  92%                                                                    
  15              18%  88%                                                                    
  16              19%  84%                                                                    
  17              20%  80%                                                                    
  18              21%  64%                                                                    
  19              22%  48%                                                                    
  20              23%  32%                                                                    
  21              24%  16%                                                                    
  22              25%   0%

 

[SatishKumar.Dx]

Thanks a lot Master,

I have many things to learn from you.

God Bless You.

Regards,
Satish