Advanced formula / function

[ClaireGoode]

Good morning all. I am new to this forum so hope that this makes sense to you!

I am trying to work out a commission/bonus model and want Excel to provide me with how much commission we should pay based on certain criteria - which includes a capped amount. We have 2 potential models to work with.

Example;

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Sample No. of Units[/TD]
[TD]8,000,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sample Commission[/TD]
[TD]£1,500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MODEL 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Base Units[/TD]
[TD]2,000,000[/TD]
[TD]The min units under which no Bonus applies[/TD]
[/TR]
[TR]
[TD]Step[/TD]
[TD]250,000[/TD]
[TD]The steps/graduations in the units that apply to the Bonus scheme[/TD]
[/TR]
[TR]
[TD]Step Bonus[/TD]
[TD]£250[/TD]
[TD]The bonus that applies for each step or graduation that is achieved[/TD]
[/TR]
[TR]
[TD]Cap[/TD]
[TD]10,000,000[/TD]
[TD]The cap that applies to the units, beyond which no additional Bonus is payable[/TD]
[/TR]
[TR]
[TD]ANSWER[/TD]
[TD][/TD]
[TD]Help here please![/TD]
[/TR]
[TR]
[TD]MODEL 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Base Units[/TD]
[TD]3,000,000[/TD]
[TD]The min units under which no Bonus applies[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Step[/TD]
[TD]250,000[/TD]
[TD]The steps or graduations in the units that apply to the Bonus scheme[/TD]
[/TR]
[TR]
[TD]Step Bonus[/TD]
[TD]10.0%[/TD]
[TD]The bonus that applies for each step or graduation that is achieved[/TD]
[/TR]
[TR]
[TD]Cap[/TD]
[TD]20,000,000[/TD]
[TD]The cap that applies to the units, beyond which no additional Bonus is payable[/TD]
[/TR]
[TR]
[TD]ANSWER[/TD]
[TD][/TD]
[TD]Help here please![/TD]
[/TR]
</tbody>[/TABLE]


Any help here would be greatly appreciated. I suspect I have attempted to make this harder than it needs to be and maybe I cannot see the simple solution!

Many thanks,

Claire Goode.

 

[AlanY]

what's the significant of

Sample Commission £1,500

in the example?

 

[ClaireGoode]

The sample commission would be payable on the Sample number of units regardless of the 2 potential commission models. So when 8m is reached £1500 would automatically be paid. This would be on top of any amount in the graduated models. Thank you.

 

[AlanY]

bonus for both models are based on the units in Cell B1 (8,000,000)
am i anywhere near?

Excel 2012
	A	B	C
1	Sample No. of Units	8,000,000
2	Sample Commission	£1,500
3
4	MODEL 1
5	Base Units	2,000,000	The min units under which no Bonus applies
6	Step	250,000	The steps/graduations in the units that apply to the Bonus scheme
7	Step Bonus	£250	The bonus that applies for each step or graduation that is achieved
8	Cap	10,000,000	The cap that applies to the units, beyond which no additional Bonus is payable
9	ANSWER	£6,000	Help here please!
10
11	MODEL 2
12	Base Units	3,000,000	The min units under which no Bonus applies
13	Step	250,000	The steps or graduations in the units that apply to the Bonus scheme
14	Step Bonus	10.00%	The bonus that applies for each step or graduation that is achieved
15	Cap	20,000,000	The cap that applies to the units, beyond which no additional Bonus is payable
16	ANSWER	10,000,000	Help here please!
Sheet9

Cell Formulas
Range	Formula
B9	=INT((MIN(B8,B1)-B5)/B6)*B7
B16	=INT(MIN(B15,B1)-B12)/B13*B14*(MIN(B15,B1)-B12)

 

[ClaireGoode]

Thank you Alan. I'll have a look and see if it's working and let you know!! It will certainly be a lot, lot closer than I could get it!!!

 

[ClaireGoode]

Hello again! Thanks again Alan, the formula for model 1 works great. However, the second model I got a bit confused on and turns out that the value in cell B2 could change and that 10% in B14 should be 10% of B2. If you, or anyone else can help with the second model that'd be great. Thank you.

 

[AlanY]

sorry that I've a off day yesterday, anyway, check this

Excel 2012
	A	B	C
1	Sample No. of Units	8,000,000
2	Sample Commission	£1,500
3
4	MODEL 1
5	Base Units	2,000,000	The min units under which no Bonus applies
6	Step	250,000	The steps/graduations in the units that apply to the Bonus scheme
7	Step Bonus	£250	The bonus that applies for each step or graduation that is achieved
8	Cap	10,000,000	The cap that applies to the units, beyond which no additional Bonus is payable
9	ANSWER	£6,000	Help here please!
10
11	MODEL 2
12	Base Units	3,000,000	The min units under which no Bonus applies
13	Step	250,000	The steps or graduations in the units that apply to the Bonus scheme
14	Step Bonus	10.00%	The bonus that applies for each step or graduation that is achieved
15	Cap	20,000,000	The cap that applies to the units, beyond which no additional Bonus is payable
16	ANSWER	£3,000	Help here please!
Sheet1

Cell Formulas
Range	Formula
B9	=INT((MIN(B8,B1)-B5)/B6)*B7
B16	=INT((MIN(B15,B1)-B12)/B13)*B14*B2

 

[AlanY]

to make it more robust when the base units is not reached, can amend the formula to

B9 =MAX(0,INT((MIN(B8,B1)-B5)/B6)*B7)
and
B16 =MAX(0,INT((MIN(B15,B1)-B12)/B13)*B14*B2)

 

[ClaireGoode]

Great, thank you Alan. Very much appreciate your help on this.

 

[AlanY]

you're welcome