KPI - Help required

[Thiroshg]

I need assistance on a formula to return the KPI scores based on the weighting on the below: The formula should read the number on under detail which in row 1 is 31 and return a kpi score from 0 in line 1 as per the scoring criteria.


A
[TABLE="width: 1014"]
<colgroup><col><col><col><col><col span="3"><col><col><col span="2"></colgroup><tbody>[TR]
[TD]KPI[/TD]
[TD]Detail[/TD]
[TD]WEIGHTING[/TD]
[TD]SCORE[/TD]
[TD]KPI[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]ABSENCE INSTANCES[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD]0 to 3[/TD]
[TD]4 to 6[/TD]
[TD]6 to 8[/TD]
[TD]8 to 10[/TD]
[TD]10 to 12[/TD]
[TD]12 +[/TD]
[/TR]
[TR]
[TD]AWOL INSTANCES[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5%[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD]0[/TD]
[TD]1 to 2[/TD]
[TD]3 to 4[/TD]
[TD]4 to 5[/TD]
[TD]5 to 6[/TD]
[TD]6+[/TD]
[/TR]
[TR]
[TD]CALLS TAKEN[/TD]
[TD="align: right"]13448[/TD]
[TD="align: right"]20%[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD]18001+[/TD]
[TD]17001-18000[/TD]
[TD]16001-17000[/TD]
[TD]15001-16000[/TD]
[TD]14001-15000[/TD]
[TD]>14000[/TD]
[/TR]
[TR]
[TD]FAXES WORKED[/TD]
[TD="align: right"]1804[/TD]
[TD="align: right"]5%[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD]3500+[/TD]
[TD]3001-3500[/TD]
[TD]2501-3000[/TD]
[TD]1501-2500[/TD]
[TD]1500-1001[/TD]
[TD]>1000[/TD]
[/TR]
[TR]
[TD]Late Coming Instances[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]15%[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD]<9[/TD]
[TD]10 to 15[/TD]
[TD]15 to 20[/TD]
[TD]20 to 30[/TD]
[TD]30 to 40[/TD]
[TD]40+[/TD]
[/TR]
[TR]
[TD]LUNCH AVR MINUTES[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]5%[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD]<24[/TD]
[TD]25 to 29[/TD]
[TD]30[/TD]
[TD]31 to 35[/TD]
[TD]35 to 40[/TD]
[TD]40 +[/TD]
[/TR]
[TR]
[TD]Other Pauses (Agents Saved)[/TD]
[TD="align: right"]-13[/TD]
[TD="align: right"]5%[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD]<-15[/TD]
[TD]-10[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]20+[/TD]
[/TR]
[TR]
[TD]TALKTIME HOURS[/TD]
[TD="align: right"]842[/TD]
[TD="align: right"]20%[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD]1601+[/TD]
[TD]1501-1600[/TD]
[TD]1401-1500[/TD]
[TD]1301-1400[/TD]
[TD]1201-1300[/TD]
[TD]>1200[/TD]
[/TR]
[TR]
[TD]TEA AVER MINUTES[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]5%[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD]<10[/TD]
[TD]11 to 14[/TD]
[TD]15[/TD]
[TD]16 to 20[/TD]
[TD]21 to 25[/TD]
[TD]30+[/TD]
[/TR]
[TR]
[TD]TOILET AVERAGE MINUTES[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5%[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD]<5[/TD]
[TD]6 to 19[/TD]
[TD]20[/TD]
[TD]21 to 25[/TD]
[TD]26 to 30[/TD]
[TD]30 +[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Hi. Its going to be difficult the way you have this set up. First things first. Your groups arent distinct. If ive taken 6 days off do i score 4 points or 3? 6 days is covered by both groups 4 to 6 and 6 to 8.

 

[Thiroshg]

[TABLE="width: 1078"]
<colgroup><col><col><col><col><col><col span="3"><col><col><col span="2"></colgroup><tbody>[TR]
[TD]3[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[TD]j[/TD]
[TD]k[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]KPI[/TD]
[TD]Detail[/TD]
[TD]WEIGHTING[/TD]
[TD]SCORE[/TD]
[TD]KPI[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]ABSENCE INSTANCES[/TD]
[TD]31[/TD]
[TD]15%[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0 to 3[/TD]
[TD]4 to 6[/TD]
[TD]6 to 8[/TD]
[TD]8 to 10[/TD]
[TD]10 to 12[/TD]
[TD]12 +[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]AWOL INSTANCES[/TD]
[TD]10[/TD]
[TD]5%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1 to 2[/TD]
[TD]3 to 4[/TD]
[TD]4 to 5[/TD]
[TD]5 to 6[/TD]
[TD]6+[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]CALLS TAKEN[/TD]
[TD]13448[/TD]
[TD]20%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]18001+[/TD]
[TD]17001-18000[/TD]
[TD]16001-17000[/TD]
[TD]15001-16000[/TD]
[TD]14001-15000[/TD]
[TD]>14000[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]FAXES WORKED[/TD]
[TD]1804[/TD]
[TD]5%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]3500+[/TD]
[TD]3001-3500[/TD]
[TD]2501-3000[/TD]
[TD]1501-2500[/TD]
[TD]1500-1001[/TD]
[TD]>1000[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Late Coming Instances[/TD]
[TD]23[/TD]
[TD]15%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]<9[/TD]
[TD]10 to 15[/TD]
[TD]15 to 20[/TD]
[TD]20 to 30[/TD]
[TD]30 to 40[/TD]
[TD]40+[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]LUNCH AVR MINUTES[/TD]
[TD]29[/TD]
[TD]5%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]<24[/TD]
[TD]25 to 29[/TD]
[TD]30[/TD]
[TD]31 to 35[/TD]
[TD]35 to 40[/TD]
[TD]40 +[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Other Pauses (Agents Saved)[/TD]
[TD]-13[/TD]
[TD]5%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]<-15[/TD]
[TD]-10[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]20+[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]TALKTIME HOURS[/TD]
[TD]842[/TD]
[TD]20%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]1601+[/TD]
[TD]1501-1600[/TD]
[TD]1401-1500[/TD]
[TD]1301-1400[/TD]
[TD]1201-1300[/TD]
[TD]>1200[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]TEA AVER MINUTES[/TD]
[TD]15[/TD]
[TD]5%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]<10[/TD]
[TD]11 to 14[/TD]
[TD]15[/TD]
[TD]16 to 20[/TD]
[TD]21 to 25[/TD]
[TD]30+[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]TOILET AVERAGE MINUTES[/TD]
[TD]7[/TD]
[TD]5%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]<5[/TD]
[TD]6 to 19[/TD]
[TD]20[/TD]
[TD]21 to 25[/TD]
[TD]26 to 30[/TD]
[TD]30 +[/TD]
[/TR]
</tbody>[/TABLE]


DOES THIS HELP--- 6 absent days will score a 3 rating

 

[steve the fish]

No it doesnt. You have ignored what i said. In your table 6 is still covered by two groups. They must be distinct. It happens countless times by the way.

 

[Thiroshg]

ohhh I see what you mean :
Updated

[TABLE="width: 1138"]
<colgroup><col><col><col><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD]3[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[TD]j[/TD]
[TD]k[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]KPI[/TD]
[TD]Detail[/TD]
[TD]WEIGHTING[/TD]
[TD]SCORE[/TD]
[TD]KPI[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]ABSENCE INSTANCES[/TD]
[TD]31[/TD]
[TD]15%[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0 to 3[/TD]
[TD]4 to 5[/TD]
[TD]6 to 8[/TD]
[TD]9 to 10[/TD]
[TD]11 to 12[/TD]
[TD]13 +[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]AWOL INSTANCES[/TD]
[TD]10[/TD]
[TD]5%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1 to 2[/TD]
[TD]3 to 4[/TD]
[TD]5 to 6[/TD]
[TD]7 to 8[/TD]
[TD]9+[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]CALLS TAKEN[/TD]
[TD]13448[/TD]
[TD]20%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]18001+[/TD]
[TD]17001-18000[/TD]
[TD]16001-17000[/TD]
[TD]15001-16000[/TD]
[TD]14001-15000[/TD]
[TD]>14000[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]FAXES WORKED[/TD]
[TD]1804[/TD]
[TD]5%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]3500+[/TD]
[TD]3001-3500[/TD]
[TD]2501-3000[/TD]
[TD]1501-2500[/TD]
[TD]1500-1001[/TD]
[TD]>1000[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Late Coming Instances[/TD]
[TD]23[/TD]
[TD]15%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]<9[/TD]
[TD]10 to 15[/TD]
[TD]16 to 20[/TD]
[TD]21 to 30[/TD]
[TD]31 to 40[/TD]
[TD]41+[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]LUNCH AVR MINUTES[/TD]
[TD]29[/TD]
[TD]5%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]<24[/TD]
[TD]25 to 29[/TD]
[TD]30[/TD]
[TD]31 to 35[/TD]
[TD]36 to 40[/TD]
[TD]41 +[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Other Pauses (Agents Saved)[/TD]
[TD]-13[/TD]
[TD]5%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]>-16[/TD]
[TD]<-16<-1[/TD]
[TD]0[/TD]
[TD]1 to 10[/TD]
[TD]11 to 15[/TD]
[TD]16+[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]TALKTIME HOURS[/TD]
[TD]842[/TD]
[TD]20%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]1601+[/TD]
[TD]1501-1600[/TD]
[TD]1401-1500[/TD]
[TD]1301-1400[/TD]
[TD]1201-1300[/TD]
[TD]>1200[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]TEA AVER MINUTES[/TD]
[TD]15[/TD]
[TD]5%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]<10[/TD]
[TD]11 to 14[/TD]
[TD]15[/TD]
[TD]16 to 20[/TD]
[TD]21 to 25[/TD]
[TD]30+[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]TOILET AVERAGE MINUTES[/TD]
[TD]7[/TD]
[TD]5%[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]<5[/TD]
[TD]6 to 19[/TD]
[TD]20[/TD]
[TD]21 to 25[/TD]
[TD]26 to 30[/TD]
[TD]30 +[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]TOTAL[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

So what is the expected results in D5:D14?

 

[steve the fish]

Ok i think i get it. Change your table to look like this:

[TABLE="width: 711"]
<tbody>[TR]
[TD]KPI[/TD]
[TD]Detail[/TD]
[TD]WEIGHTING[/TD]
[TD]SCORE[/TD]
[TD]KPI[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ABSENCE INSTANCES[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]AWOL INSTANCES[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]CALLS TAKEN[/TD]
[TD="align: right"]13448[/TD]
[TD="align: right"]20%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]18001[/TD]
[TD="align: right"]17001[/TD]
[TD="align: right"]16001[/TD]
[TD="align: right"]15001[/TD]
[TD="align: right"]14001[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]FAXES WORKED[/TD]
[TD="align: right"]1804[/TD]
[TD="align: right"]5%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3501[/TD]
[TD="align: right"]3001[/TD]
[TD="align: right"]2501[/TD]
[TD="align: right"]1501[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Late Coming Instances[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]15%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD]LUNCH AVR MINUTES[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]5%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD]Other Pauses (Agents Saved)[/TD]
[TD="align: right"]-13[/TD]
[TD="align: right"]5%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-1000[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]TALKTIME HOURS[/TD]
[TD="align: right"]842[/TD]
[TD="align: right"]20%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1601[/TD]
[TD="align: right"]1501[/TD]
[TD="align: right"]1401[/TD]
[TD="align: right"]1301[/TD]
[TD="align: right"]1201[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]TEA AVER MINUTES[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]5%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]TOILET AVERAGE MINUTES[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]5%[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]

Then in D5 place this:

=IF(F5>G5,INDEX($F$4:$K$4,MATCH(1,INDEX(--(F5:K5 < B5),0),0)),LOOKUP(B5,F5:K5,$F$4:$K$4))<b5),0),0)),lookup(b5,f5:k5,$f$4:$k$4))


Copy down.</b5),0),0)),lookup(b5,f5:k5,$f$4:$k$4))