Calculating rebate bands based on total sales

[GarryFreeman]

I am hoping that someone can help me with this.
In the image below you can see that I am trying to reward based on increased purchases, however this agreement is not a back to zero agreement.
In the example I am trying to reward the amount spent in a particular band between the 2 parameters. For example the difference between 95,000 and 99,999 is £4,999 which then attracts a payment of 249.95 (4,999 x 5%)
However my formula fails when the final total spend is between the bands as you can see in G24. What I need to show is £102,000 x 10%. Then M6 sums column I
If the T/O was for example £77,000 then this would attract all the cash payments in column I above row 14, add £2000 of T/O at 5% and everything below would remain blank "" with no reference to FALSE if possible

I thank you in advance for your help

Garry

 

[Dave Patton]

What is the correct not rounded total for 64,999.50?
What is the correct not rounded total for 104,999?

 

[GarryFreeman]

What is the correct not rounded total for 64,999.50?
What is the correct not rounded total for 104,999?

Sorry Dave, I did not understand your question?

 

[Dave Patton]

Your example is not clear. If you have clear brackets and rates one calculation can provide the amount for the rebate.
Can you manually complete the arithmetic for 64,999.50? What is the rebate?
or
Review and edit the following
B3 names the ranges. With B3 you would not have to leave the table of brackets and rates on the sheet.
Column H is not required; it just shows the arithmetic.

T202011b.xlsm
	A	B	C	D	E	F	G	H
1	Sales	102,000.00		Bracket	Rate	Rate Diff
2	Rebate	2,400.00		0.00	1%	1%		600.00
3		2,400.00		60,000.00	2%	1%		100.00
4				65,000.00	3%	1%		150.00
5				70,000.00	4%	1%		200.00
6				75,000.00	5%	1%		1,350.00
7								2,400.00
4a

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT(--(B1>D2:D6),B1-D2:D6,F2:F6)
B3	B3	=SUMPRODUCT(--(B1>aB),B1-aB,aR)
F2:F6	F2	=E2-N(E1)
H2:H6	H2	=(MIN($B$1,D3)-D2)*E2
H7	H7	=SUM(H2:H6)

 

[GarryFreeman]

What is the correct not rounded total for 64,999.50?
What is the correct not rounded total for 104,999?

Your example is not clear. If you have clear brackets and rates one calculation can provide the amount for the rebate.
Can you manually complete the arithmetic for 64,999.50? What is the rebate?
or
Review and edit the following
B3 names the ranges. With B3 you would not have to leave the table of brackets and rates on the sheet.
Column H is not required; it just shows the arithmetic.

T202011b.xlsm
	A	B	C	D	E	F	G	H
1	Sales	102,000.00		Bracket	Rate	Rate Diff
2	Rebate	2,400.00		0.00	1%	1%		600.00
3		2,400.00		60,000.00	2%	1%		100.00
4				65,000.00	3%	1%		150.00
5				70,000.00	4%	1%		200.00
6				75,000.00	5%	1%		1,350.00
7								2,400.00
4a

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT(--(B1>D2:D6),B1-D2:D6,F2:F6)
B3	B3	=SUMPRODUCT(--(B1>aB),B1-aB,aR)
F2:F6	F2	=E2-N(E1)
H2:H6	H2	=(MIN($B$1,D3)-D2)*E2
H7	H7	=SUM(H2:H6)

Thanks for you patience Dave..

Below is what I am trying to achieve, I am trying to get to a formula that will sum the correct totals based on the figure in D4 (120000 in this instance.
Using the first example below every band up to row 24 achieves the full amount of turnover applied at the relevant rate. However on line 26 it only achieves 2000.

In the second example the same can be said but only up to line 42 where it achieves 5% of 1000.
I am looking for something that will auto populate every band based on the total t/o.

Hope this is clearer?

 

[GarryFreeman]

Sorry my typo D4 = 102000

 

[Dave Patton]

I provided a suggestion on how you can complete the calculation with one formula.
Why do you want separate calculations for each band?
I did include the arithmetic for each band to show that the formula works.
I also asked for the calculation for 2 different amounts to have a correct target.

I apologize for thinking that you required a calculation for amount of rebate for a specified amount.

Perhaps someone else will follow your example.

 

[GarryFreeman]

I provided a suggestion on how you can complete the calculation with one formula.
Why do you want separate calculations for each band?
I did include the arithmetic for each band to show that the formula works.
I also asked for the calculation for 2 different amounts to have a correct target.

I apologize for thinking that you required a calculation for amount of rebate for a specified amount.

Perhaps someone else will follow your example.

Thanks for your help Dave

Unfortunately I need it split out by bands to demonstrate how the total figure was calculated.
Clearly this can be done manual was just looking for a way to automate the process as I have many accounts to provide this detail to.

 

[Dave Patton]

You can copy post #4 see the icon below the f(x). Paste it into a clean sheet.
You can review the formulas. The XL2BB did not copy the named data so ignore that formula.

 

[Dave Patton]

T202011b.xlsm
	A	B	C	D	E	F
1	Sales	102,000.00		Bracket	Rate
2
3	Rebate	2,400.00		0.00	1%	600.00
4				60,000.00	2%	100.00
5				65,000.00	3%	150.00
6				70,000.00	4%	200.00
7				75,000.00	5%	1,350.00
8				1E+308		2,400.00
9
4a

Cell Formulas
Range	Formula
B3	B3	=SUMPRODUCT(--(B1>D3:D7),B1-D3:D7,E3:E7-E2:E6)
F3:F7	F3	=(MIN($B$1,D4)-D3)*E3
F8	F8	=SUM(F3:F7)