Income-Tax like graduated pricing (kinda)

[inquisitor]

Greetings,

I can find information about how to build a formula to calculate income tax, but I can't seem to get them to work for my use case, which is admittedly a little different. Let's say I have a price chart:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]From[/TD]
[TD]To[/TD]
[TD]Price Ea[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]1000[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]5000[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]5001[/TD]
[TD]10000[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]10001[/TD]
[TD]25000[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]25001[/TD]
[TD]50000[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD]50001[/TD]
[TD]100000[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD]100001[/TD]
[TD]250000[/TD]
[TD]35[/TD]
[/TR]
</tbody>[/TABLE]

The way I'd like this to work is every unit up to 1000 costs 200 each. Every unit beyond that up to 5000 costs 50 each. Every unit beyond 5000 up to 10,000 costs 45 each. And so on. A few examples to make this clear:

500 units should cost 100,000 (500x200).
1000 units should cost 200,000 (1000x200).
2000 units should cost 250,000 (1000x200 + 1000x50).
7500 units should cost 512,500 (1000x200 + 4000x50 + 2500x45).

What's the most efficient way to solve this problem with a formula? If not the most efficient, what's a pragmatic way to do it? Appreciate any help/guidance.

 

[mrhstn]

With a formula you can try like this

Book1
	A	B	C
1	From		Price Ea
2		0	200
3	0	1000	50
4	1001	5000	45
5	5001	100001	40
6	10001	25000	38
7	25001	50000	36
8	50001	100000	35
9	100001	250000
10
11
12	500	100,000
13	1000	200,000
14	2000	250,000
15	7500	512,500
16
Sheet1

Cell Formulas
Range	Formula
B12	{=SUM(IF(IF(A$12>=B3:B9,B3:B9-B2:B8,$A$12-B2:B8)>0,IF(A$12>=B3:B9,B3:B9-B2:B8,$A$12-B2:B8),0)*C2:C8)}
B13	{=SUM(IF(IF(A$13>=B3:B9,B3:B9-B2:B8,$A$13-B2:B8)>0,IF(A$13>=B3:B9,B3:B9-B2:B8,$A$13-B2:B8),0)*C2:C8)}
B14	{=SUM(IF(IF(A$14>=B3:B9,B3:B9-B2:B8,$A$14-B2:B8)>0,IF(A$14>=B3:B9,B3:B9-B2:B8,$A$14-B2:B8),0)*C2:C8)}
B15	{=SUM(IF(IF(A$15>=B3:B9,B3:B9-B2:B8,$A$15-B2:B8)>0,IF(A$15>=B3:B9,B3:B9-B2:B8,$A$15-B2:B8),0)*C2:C8)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[inquisitor]

Outstanding! Thank you very much for your help.

 

[mrhstn]

An alternative to the above...

Book1
	A	B	C
1	From	to	Price
2
3		0	200
4	0	1000	50
5	1001	5000	45
6	5001	100001	40
7	10001	25000	38
8	25001	50000	36
9	50001	100000	35
10	100001	250000
11
12
13	500	100000
14	1000	200000
15	2000	250000
16	7500	512500
Sheet1

Cell Formulas
Range	Formula
B13	=SUMPRODUCT(--(A13>B$3:B$10),A13-B$3:B$10,C$3:C$10-C$2:C$9)
B14	=SUMPRODUCT(--(A14>B$3:B$10),A14-B$3:B$10,C$3:C$10-C$2:C$9)
B15	=SUMPRODUCT(--(A15>B$3:B$10),A15-B$3:B$10,C$3:C$10-C$2:C$9)
B16	=SUMPRODUCT(--(A16>B$3:B$10),A16-B$3:B$10,C$3:C$10-C$2:C$9)

 

[Dave Patton]

Excel 2010
	A	B	C	D
1		Brackets
2		Volume	Price	Diff
3		0	200	200
4		1,000	50	-150
5		5,000	45	-5
6		10,000	40	-5
7		25,000	38	-2
8		50,000	36	-2
9		100,000	35	-1
10		250,000
11
12
13	500	100,000.00
14	1000	200,000.00
15	250000	9,225,000.00
4dd

Cell Formulas
Range	Formula
D3	=C3-N(C2)
B13	=SUMPRODUCT(--(A13>B$3:B$9),A13-B$3:B$9,$D$3:$D$9)