collininspace
New Member
- Joined
- Dec 16, 2010
- Messages
- 1
Bonus Qualification Formula needed:
I have multiple divisions who can qualify for different bonus levels depending on two factors: how much their revenue grew (%) and profit margin % improvement (in % points, i.e. 3PPs better than last year). I have pasted the 16 different levels below.
For example, if a division had revenue growth of 2.5% and margin improvement of 3.5 percentage points, they qualify for 35% of their bonus target. How can I write a formula that will allow me to quickly analyze these 2 factors to determine their bonus qualification?
Revenue Growth % Margin % Growth
BonusLevel
Min Max Min Max % of Bonus Earned
1 0.000% 2.999% 0.000% 0.999% 0%
2 0.000% 2.999% 1.000% 1.999% 15%
3 0.000% 2.999% 2.000% 2.999% 25%
4 0.000% 2.999% 3.000% 3.999% 35%
5 3.000% 5.990% 0.000% 0.999% 10%
6 3.000% 5.990% 1.000% 1.999% 30%
7 3.000% 5.990% 2.000% 2.999% 50%
8 3.000% 5.990% 3.000% 3.999% 70%
9 6.000% 8.990% 0.000% 1.999% 25%
10 6.000% 8.990% 2.000% 2.999% 100%
11 6.000% 8.990% 3.000% 3.999% 110%
12 6.000% 8.990% 4.000% or more 125%
13 9.000% or more 0.000% 2.249% 40%
14 9.000% or more 2.250% 3.249% 115%
15 9.000% or more 3.250% 4.249% 125%
16 9.000% or more 4.250% or more 150%
I hope to avoid a lot of nested IF statements, and suspect some usage of LOOKUP or arrays might be in order. This is my first post, so I apologize if this is too simple for the knowledge base out there. I appreciate your help!
I have multiple divisions who can qualify for different bonus levels depending on two factors: how much their revenue grew (%) and profit margin % improvement (in % points, i.e. 3PPs better than last year). I have pasted the 16 different levels below.
For example, if a division had revenue growth of 2.5% and margin improvement of 3.5 percentage points, they qualify for 35% of their bonus target. How can I write a formula that will allow me to quickly analyze these 2 factors to determine their bonus qualification?
Revenue Growth % Margin % Growth
BonusLevel
Min Max Min Max % of Bonus Earned
1 0.000% 2.999% 0.000% 0.999% 0%
2 0.000% 2.999% 1.000% 1.999% 15%
3 0.000% 2.999% 2.000% 2.999% 25%
4 0.000% 2.999% 3.000% 3.999% 35%
5 3.000% 5.990% 0.000% 0.999% 10%
6 3.000% 5.990% 1.000% 1.999% 30%
7 3.000% 5.990% 2.000% 2.999% 50%
8 3.000% 5.990% 3.000% 3.999% 70%
9 6.000% 8.990% 0.000% 1.999% 25%
10 6.000% 8.990% 2.000% 2.999% 100%
11 6.000% 8.990% 3.000% 3.999% 110%
12 6.000% 8.990% 4.000% or more 125%
13 9.000% or more 0.000% 2.249% 40%
14 9.000% or more 2.250% 3.249% 115%
15 9.000% or more 3.250% 4.249% 125%
16 9.000% or more 4.250% or more 150%
I hope to avoid a lot of nested IF statements, and suspect some usage of LOOKUP or arrays might be in order. This is my first post, so I apologize if this is too simple for the knowledge base out there. I appreciate your help!
