Help needed :) - Total cost by tier

[Welsh Mark3]

I'm looking to create a formula that would calculate costs based on 'Report Used'. For example, if reports used is 2000 * $7.50, If 6000 then 5000 * 7.5 plus 6000-5000*$5.00, If 11000 then 5000 * 7.5 plus 5 *$5.00 plus 11000- 10000 *$2.5

Can someone help?

 

[jasonb75]

Have a look at the answers here, Quite the conundrum...
If you need help adapting it to your data, I'll be happy to help with that.

 

[Eric W]

Here are 2 ways. If you build your rates into a table, like I did in D1:G4, you can use the B7 formula. This makes the formula shorter, and you only have to change the table if your rates change in the future. If you want to build it all in the formula, you can use the C7 formula. In both cases you should be able to see the pattern so you can adapt it as needed.

Book1
	A	B	C	D	E	F	G
1				Lower	Upper	Rate	Delta Rate
2				0	5000	$ 7.50	7.5
3				5000	10000	$ 5.00	-2.5
4				10000	9999999	$ 2.50	-2.5
5
6	Reports Used	11000
7	Total cost	$ 65,000.00	$ 65,000.00
Sheet9

Cell Formulas
Range	Formula
G2:G4	G2	=F2-N(F1)
B7	B7	=SUMPRODUCT(B6-D2:D4,--(B6>D2:D4),G2:G4)
C7	C7	=MEDIAN(0,B6-0,5000-0)*7.5+MEDIAN(0,B6-5000,10000-5000)*5+MEDIAN(0,B6-10000,9999999-10000)*2.5