Excel formula help

[braidp]

Hi I was hoping someone could help me we a formula that I've been working on

I am trying to build a progressive commission tier table and have it pretty much working other than the first payment level.

When I put £1000 it calculates at zero, as shown below
And if I put in £2000 of sales generated it only calculates £1000 x 2% rather than the full £2000

Can someone please help with this?

 

[Sufiyan97]

Like this?

Book1
	A	B	C	D	E	F	G
1
2		Sales	1000
3
4		Commission
5
6		Tier	Reveue Target		Tier Percentage
7		1	1000	2000	2%	20
8		2	2000	5000	3%	90
9		3	5000	10000	5%	250
10		4	1000	50000	8%	3920
11
12
13
Sheet1

Cell Formulas
Range	Formula
F7:F10	F7	=MAX(0,MAX(D7,$C$2)-C7)*E7

 

[Joe4]

That looks correct to me.

According to your grid, and the way the formula is written, it looks like it is doing what it is supposed to.
Your grid seems to be saying that for values ABOVE 1000 and less then 2000, apply 2%.
Since 1000 is NOT above 1000, the amount would be 0.
Since 2000 is exactly 1000 above 1000, the 2% would be applied to 1000, not 2000.

Is that not what you want to happen?
If not, please explain exactly how you want this to work, and show us an example calculation with a large number, like 15,000 so we can understand your exact logic.

 

[braidp]

Hi both thanks for the advice, I've managed to get Tier 1 working as I wanted now. by including an IF statement at the start and deleting the substraction of C7 from the first row. The rest of the tiers I've left the original formula in.

 

[braidp]

Like this?

Book1
	A	B	C	D	E	F	G
1
2		Sales	1000
3
4		Commission
5
6		Tier	Reveue Target		Tier Percentage
7		1	1000	2000	2%	20
8		2	2000	5000	3%	90
9		3	5000	10000	5%	250
10		4	1000	50000	8%	3920
11
12
13
Sheet1

Cell Formulas
Range	Formula
F7:F10	F7	=MAX(0,MAX(D7,$C$2)-C7)*E7

Hi Sufiyan,

Thak you for your reply however that wasn't quite what I was hoping to do with it but really appreciate you trying to help.

I've managed to figure out a solution to my problem below