Water Consumption Tier Pricing

[CascadeClerk]

I need assistance in creating a formula for a tier pricing.

Tier 1 0-5,000 gallons equals $0.75 per 1,000 gallons
Tier 2 5,001 to 10,000 gallons equals $0.85 per 1,000 gallons
Tier 3 10,001 to 20,000 gallons equals $1.25 per 1,000 gallons
Tier 4 20,001 to 30,000 gallons equals $1.50 per 1,000 gallons
Tier 5 30,001 and more gallons equals $2.00 per 1,000 gallons

So in essence: Water user "A" uses 31,000 gallons of water he would be billed a water usage rate of $42.50 ($3.75 for the first 5,000 gallons, $4.25 for the next 5,000 gallons, $12.50 for the next 10,000 gallons and $22.00 for the last 11,000 gallons equally 31,000 gallons consumed)

[shg]

	A	B	C	D
1	Usage	Rate	Delta
2	0	$ 0.00075	$ 0.00075	C2: =B2 - N(B1)
3	5,000	$ 0.00085	$ 0.00010
4	10,000	$ 0.00125	$ 0.00040
5	20,000	$ 0.00150	$ 0.00025
6	30,000	$ 0.00200	$ 0.00050
7
8	Usage	Cost
9	1,000	$ 0.75		B9: =SUMPRODUCT( (A9>$A$2:$A$6) * (A9 - $A$2:$A$6) * $C$2:$C$6)
10	2,000	$ 1.50
11	5,000	$ 3.75
12	10,000	$ 8.00
13	20,000	$ 20.50
14	50,000	$ 75.50
15	100,000	$ 175.50

[CascadeClerk]

THANK YOU thank you THANK you

[shg]

You're welcome.