Multiple Criteria selection in a tier system

[LIMEL]

Hi,

I am working on a bonus worksheet, however the multiple criteria make it super manual and prone to error. Is there any good formula out there to solve my problem?

It seems simple, to pick the bonus amount based on the sales value and achievement rate, see below example.

For example,

Salesman A sells 1M, and the growth rate from last year is 36%, so his return bonus is 40,000
Salesman B sells 2.6M, and the growth rate from last year is 20%, so his return bonus is 12,000


Growth % Bonus scheme (in 000)
52%-55% 67 107 142 156 171 186
49%-52% 62 96 130 143 156 170
46%-49% 58 88 120 132 144 156
43%-46% 54 81 110 121 132 143
40%-43% 50 75 100 110 120 130
35%-40% 40 60 80 88 96 104
30%-35% 25 38 50 55 60 65
25%-30% 0 0 20 22 24 52
20%-25% 0 0 10 11 12 39

Sales Base (in M) 0.8-1.2M 1.2-1.6M 1.6-2M 2-2.4M 2.4-2.8M 2.8M - 3.2M


May I know what is the good formula to return the bonus based on the above criteria?

Thanks in advance!

[Mysore]

Please upload a sample file.

[LIMEL]

Hello, I wanted to upload the excel file, but I am not sure where to find the file upload place, please help...

[Mysore]

Click on Go Advanced tab below and the attach file tool is displayed at the toolbar and you follow through

[LIMEL]

Hello,

Any idea why the file taking so long to load, it has been 5 minute now... and still running...

[haripopuri]

See if this one helps.

[Punnam]

Hi ,

Check the file size it should not be more than 1 Mb.

Punnam

[LIMEL]

Hi Haripopuri,

Thanks so much, my problem is solved! :-)

And Punnam,
At this point, the file upload is still loading... the file is really small 13kb... not sure what happen...

Not forgetting Mysore, thanks!

[LIMEL]

And Haripopuri, how do I rate you a "Star"?

[haripopuri]

You can just click on the add reputation button below the each post.