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

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

<colgroup><col><col><col><col><col span="3"><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[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]

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

<colgroup><col><col><col><col><col><col span="3"><col><col><col span="2"></colgroup><tbody>
</tbody>

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

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

<colgroup><col><col><col><col><col><col><col span="6"></colgroup><tbody>
</tbody>

 

[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:

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

<tbody>
</tbody>

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))