Creating a table with graduated rebate based on items purchased

[James_8]

I am trying to create a table for a customer based on an agreement we have where we will give a discount based on the purchase amount throughout the year.

Here are my parameters

Up to $10,000 0% discount
Between $10,000 and $25,000 3.5% discount
Between $25,000 and $50,000 5% discount
Between $50,000 and $100,000 7.5% discount
over $100,000 10% discount

These are made up numbers but this is essentially what I want to do. I would like to refer to a cell that sums the total purchased amount on the year and based on that it will compute applicable rebates at each level.

Please help

 

[tybaltlives]

something like this? you enter the purchase amount in A2. Your limits and discounts are in the table. The total discount is in cell D10.

A function could be written to calculate and return the discount amount if you had a table of purchase amouts from different customers.

Excel 2010
	A	B	C	D
1	Purchase Amt
2	110000
3
4	Limits	Discount	Portion	Discount
5	0	-	10000	0
6	10000	0.035	15000	525
7	25000	0.050	25000	1250
8	50000	0.075	50000	3750
9	100000	0.100	10000	1000
10			Tot Disc	6525
Sheet1 (3)

Cell Formulas
Range	Formula
C5	=IF($A$2-A5>0,$A$2-A5-SUM(C6:$C$10),0)
C6	=IF($A$2-A6>0,$A$2-A6-SUM(C7:$C$10),0)
C7	=IF($A$2-A7>0,$A$2-A7-SUM(C8:$C$10),0)
C8	=IF($A$2-A8>0,$A$2-A8-SUM(C9:$C$10),0)
C9	=IF($A$2-A9>0,$A$2-A9-SUM(C10:$C$10),0)
D5	=B5*C5
D6	=B6*C6
D7	=B7*C7
D8	=B8*C8
D9	=B9*C9
D10	=SUM(D5:D9)

 

[James_8]

Thank you. I was able to get it to work. I'll try this way too to see if I can get it to work.