Incentive Calculation Based on multiple parameter

[JMani]

Hi,

Need help to find a suitable formula to make the calculation automatic based on the numbers they score for each heading. Please see the sample below. First table is the incentive matrix and the second is a sample score for agents. I need the total payout to be generated based on the bracket they fall in for each parameter and the based on the weightage. Max payout is 5000. The one gets the perfect 10 in all the parameters will be getting the maximum incentive of 5000.

Please let me know if the sample is clear enough to understand and if not, I will once explain.

Thanks in advance for your help and this will help me very much to make the calculations easy and quick.

[TABLE="width: 606"]
<colgroup><col><col><col><col span="2"><col span="2"><col></colgroup><tbody>[TR]
[TD]Weightage[/TD]
[TD]15%[/TD]
[TD]20%[/TD]
[TD]10%[/TD]
[TD]15%[/TD]
[TD]10%[/TD]
[TD]20%[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD]Scores[/TD]
[TD]Param#1[/TD]
[TD]Param#2[/TD]
[TD]Param#3[/TD]
[TD]Param#4[/TD]
[TD]Param#5[/TD]
[TD]Param#6[/TD]
[TD]Param#7[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]>=95%[/TD]
[TD]>=12[/TD]
[TD]0%[/TD]
[TD]>=8 hrs[/TD]
[TD]>=95%[/TD]
[TD]100%[/TD]
[TD]<=5%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]>=90&<95[/TD]
[TD]>=10&<12[/TD]
[TD]1%[/TD]
[TD]>=7 & <8[/TD]
[TD]>=90&<95[/TD]
[TD]>=95%&<100 [/TD]
[TD]6% - 6.99%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]>=85&<90[/TD]
[TD]>=8&<10[/TD]
[TD]2%[/TD]
[TD]>=6 & <7[/TD]
[TD]>=85&<90[/TD]
[TD]>=90%&<95[/TD]
[TD]7% - 7.99%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]>=80%&<85[/TD]
[TD]>=6&<8[/TD]
[TD]3%[/TD]
[TD]>=5 & <6[/TD]
[TD]>=80%&<85[/TD]
[TD]>=85%&<90[/TD]
[TD]8% - 8.99%[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]>=75%&<80%[/TD]
[TD]>=5&<6[/TD]
[TD]4%[/TD]
[TD]>=4 & <5[/TD]
[TD]>=75%&<80%[/TD]
[TD]>=80%&<85%[/TD]
[TD]9% - 9.99%[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]<75%[/TD]
[TD]<5[/TD]
[TD]5%[/TD]
[TD]<4[/TD]
[TD]<75%[/TD]
[TD]< 80%[/TD]
[TD]≥ 10%[/TD]
[/TR]
</tbody>[/TABLE]


Below given is a sample performance numbers for the calculation.

[TABLE="width: 690"]
<colgroup><col><col><col><col span="2"><col span="2"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Param#1[/TD]
[TD]Param#2[/TD]
[TD]Param#3[/TD]
[TD]Param#4[/TD]
[TD]Param#5[/TD]
[TD]Param#6[/TD]
[TD]Param#7[/TD]
[TD]Total Payout[/TD]
[/TR]
[TR]
[TD]Agent#1[/TD]
[TD]89%[/TD]
[TD]6[/TD]
[TD]0%[/TD]
[TD]8[/TD]
[TD]98%[/TD]
[TD]90%[/TD]
[TD]3%[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Agent#2[/TD]
[TD]98%[/TD]
[TD]4[/TD]
[TD]0%[/TD]
[TD]7.5[/TD]
[TD]89%[/TD]
[TD]87%[/TD]
[TD]6%[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Agent#3[/TD]
[TD]78%[/TD]
[TD]15[/TD]
[TD]1%[/TD]
[TD]5.5[/TD]
[TD]90%[/TD]
[TD]67%[/TD]
[TD]4%[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Agent#4[/TD]
[TD]56%[/TD]
[TD]10[/TD]
[TD]3%[/TD]
[TD]7[/TD]
[TD]95%[/TD]
[TD]76%[/TD]
[TD]2%[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]


Best,
JM

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][td="bgcolor:#C0C0C0"]

I​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Wgt​

[/td][td]

15%​

[/td][td]

20%​

[/td][td]

10%​

[/td][td]

15%​

[/td][td]

10%​

[/td][td]

20%​

[/td][td]

10%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td="bgcolor:#F3F3F3"]

Scores​

[/td][td="bgcolor:#F3F3F3"]

Param#1​

[/td][td="bgcolor:#F3F3F3"]

Param#2​

[/td][td="bgcolor:#F3F3F3"]

Param#3​

[/td][td="bgcolor:#F3F3F3"]

Param#4​

[/td][td="bgcolor:#F3F3F3"]

Param#5​

[/td][td="bgcolor:#F3F3F3"]

Param#6​

[/td][td="bgcolor:#F3F3F3"]

Param#7​

[/td][td="bgcolor:#F3F3F3"]

Total​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

0​

[/td][td]

0%​

[/td][td]

0​

[/td][td]

5%​

[/td][td]

0​

[/td][td]

0%​

[/td][td]

0​

[/td][td]

10%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

1​

[/td][td]

75%​

[/td][td]

5​

[/td][td]

4%​

[/td][td]

4​

[/td][td]

75%​

[/td][td]

80%​

[/td][td]

9%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

2​

[/td][td]

80%​

[/td][td]

6​

[/td][td]

3%​

[/td][td]

5​

[/td][td]

80%​

[/td][td]

85%​

[/td][td]

8%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

3​

[/td][td]

85%​

[/td][td]

8​

[/td][td]

2%​

[/td][td]

6​

[/td][td]

85%​

[/td][td]

90%​

[/td][td]

7%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

4​

[/td][td]

90%​

[/td][td]

10​

[/td][td]

1%​

[/td][td]

7​

[/td][td]

90%​

[/td][td]

95%​

[/td][td]

6%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

5​

[/td][td]

95%​

[/td][td]

12​

[/td][td]

0%​

[/td][td]

8​

[/td][td]

95%​

[/td][td]

100%​

[/td][td]

0%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]Agent#1[/td][td]

89%​

[/td][td]

6​

[/td][td]

0%​

[/td][td]

8.0​

[/td][td]

98%​

[/td][td]

90%​

[/td][td]

3%​

[/td][td="bgcolor:#E5E5E5"]

3,700 ​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]Agent#2[/td][td]

98%​

[/td][td]

4​

[/td][td]

0%​

[/td][td]

7.5​

[/td][td]

89%​

[/td][td]

87%​

[/td][td]

6%​

[/td][td="bgcolor:#E5E5E5"]

2,950 ​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]Agent#3[/td][td]

78%​

[/td][td]

15​

[/td][td]

1%​

[/td][td]

5.5​

[/td][td]

90%​

[/td][td]

67%​

[/td][td]

4%​

[/td][td="bgcolor:#E5E5E5"]

2,750 ​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]Agent#4[/td][td]

56%​

[/td][td]

10​

[/td][td]

3%​

[/td][td]

7.0​

[/td][td]

95%​

[/td][td]

76%​

[/td][td]

2%​

[/td][td="bgcolor:#E5E5E5"]

2,600 ​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]Agent#4[/td][td]

95%​

[/td][td]

12​

[/td][td]

0%​

[/td][td]

8.0​

[/td][td]

95%​

[/td][td]

100%​

[/td][td]

0%​

[/td][td="bgcolor:#E5E5E5"]

5,000 ​

[/td][/tr]
[/table]

The ugly array formula in I10 and copied down is

=1000*SUMPRODUCT($B$1:$H$1,
CHOOSE({1,2,3,4,5,6,7},
INDEX($A$3:$A$8, MATCH(B10, B$3:B$8)),
INDEX($A$3:$A$8, MATCH(C10, C$3:C$8)),
INDEX($A$3:$A$8, MATCH(TRUE, 1 - D10 <= 1 - D$3:D$8, 0)),
INDEX($A$3:$A$8, MATCH(E10, E$3:E$8)),
INDEX($A$3:$A$8, MATCH(F10, F$3:F$8)),
INDEX($A$3:$A$8, MATCH(G10, G$3:G$8)),
INDEX($A$3:$A$8, MATCH(TRUE, 1 - H10 <= 1 - H$3:H$8, 0))))

 

[JMani]

Dear shg,

Thank you for the help. I received an error message in the line INDEX($A$3:$A$8, MATCH(TRUE, 1 - D10 <= 1 - D$3:D$8, 0)). Error was from 1-D$3:D$8 section.
Please check and help me.

Thank you,
JM

 

[shg]

Paste the formula in the formula bar, press and hold the Control and Shift keys, then press Enter.

 

[JMani]

Hi shg,

I got it. I had to ctrl+shift+enter. It is working fine. Thank you so much. I will check in detail and if need more help, will let you know. Thank you once again. Great help )))

JM

 

[JMani]

Hi, I have a question. Now the maximum payout is kept at 5000. It may go up to 7500 and/or 10000 in future. How can i change the payout calculation? Please clarify.

 

[shg]

See that 1000 multiplier at the beginning of the formula?

 

[JMani]

Hi shg, I faced one error and need you help to rectify it. Even if I remove all values, the payout should be "0". But it shows, "1000". Please check and let me know.

Thank you.
JM

 

[shg]

I don't think so:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][td="bgcolor:#C0C0C0"]

I​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Wgt​

[/td][td]

15%​

[/td][td]

20%​

[/td][td]

10%​

[/td][td]

15%​

[/td][td]

10%​

[/td][td]

20%​

[/td][td]

10%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td="bgcolor:#F3F3F3"]

Scores​

[/td][td="bgcolor:#F3F3F3"]

Param#1​

[/td][td="bgcolor:#F3F3F3"]

Param#2​

[/td][td="bgcolor:#F3F3F3"]

Param#3​

[/td][td="bgcolor:#F3F3F3"]

Param#4​

[/td][td="bgcolor:#F3F3F3"]

Param#5​

[/td][td="bgcolor:#F3F3F3"]

Param#6​

[/td][td="bgcolor:#F3F3F3"]

Param#7​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

0​

[/td][td]

0%​

[/td][td]

0​

[/td][td]

5%​

[/td][td]

0​

[/td][td]

0%​

[/td][td]

0%​

[/td][td]

10%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

1​

[/td][td]

75%​

[/td][td]

5​

[/td][td]

4%​

[/td][td]

4​

[/td][td]

75%​

[/td][td]

80%​

[/td][td]

9%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

2​

[/td][td]

80%​

[/td][td]

6​

[/td][td]

3%​

[/td][td]

5​

[/td][td]

80%​

[/td][td]

85%​

[/td][td]

8%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

3​

[/td][td]

85%​

[/td][td]

8​

[/td][td]

2%​

[/td][td]

6​

[/td][td]

85%​

[/td][td]

90%​

[/td][td]

7%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

4​

[/td][td]

90%​

[/td][td]

10​

[/td][td]

1%​

[/td][td]

7​

[/td][td]

90%​

[/td][td]

95%​

[/td][td]

6%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

5​

[/td][td]

95%​

[/td][td]

12​

[/td][td]

0%​

[/td][td]

8​

[/td][td]

95%​

[/td][td]

100%​

[/td][td]

0%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]

Total​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]Agent#1[/td][td]

0%​

[/td][td]

0​

[/td][td]

5%​

[/td][td]

0.0​

[/td][td]

0%​

[/td][td]

0%​

[/td][td]

10%​

[/td][td="bgcolor:#E5E5E5"]

0 ​

[/td][/tr]
[/table]