Water Billing Rates Formula

[EvanBerrett]

I am trying to create a formula that allows me to evaluate scenarios for water billing rates. These rates use a progressive tier system and I'm trying to calculate the monthly bill based on total water consumption. So for example:

Consumption: 200 Kgal

Tiers:

A: 0 - 6 Kgal

B: 6 - 12 Kgal

C: 12 - 18 Kgal

D: 18+ Kgal



Rates:

A: $0.80/Kgal

B: $0.85/Kgal

C: $0.90/Kgal

D: $0.95/Kgal



So assuming 200 Kgals used, the first 6 Kgal will be at $0.80/Kgal, the second 6 Kgal will be billed at $0.85/Kgal, the third 6 Kgal will be billed at $0.90/Kgal, and anything over 18 Kgal will be billed at $0.95/Kgal.

Any help with a formula that can help me accomplish this would be great. Thanks in Advance.

 

[Dave Patton]

Thanks for feedback.
I cleaned up the post.

T202003a.xlsm
	B	C	D	E	F
1	Number Kgal	100.00
2	Amount	93.20	93.20	93.20	93.20
3
4	Units	Rates	Rate Diff
5	blank row
6	0	0.80	0.80
7	6	0.85	0.05
8	12	0.90	0.05
9	18	0.95	0.05
7a

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT(--(C1>=B6:B9),--(C1-B6:B9),D6:D9)
D2	D2	=SUMPRODUCT(--(C1>B6:B9),--(C1-B6:B9),(C6:C9-C5:C8))
E2	E2	=SUMPRODUCT(--(C1>{0;6;12;18}),--(C1-{0;6;12;18}),{0.8;0.05;0.05;0.05})
F2	F2	=(C1>0)*C1*0.8+(C1>6)*(C1-6)*0.05+(C1>12)*(C1-12)*0.05+(C1>18)*(C1-18)*0.05
D6	D6	=C6-N(C4)
D7:D9	D7	=C7-N(C6)

 

[Fluff]

@EvanBerrett
Please take the time to read our rules & in future abide by them
Also posted here Water Billing Rates Formula [SOLVED]

Have you posted this anywhere else?

 

[EvanBerrett]

Thanks for feedback.
I cleaned up the post.

T202003a.xlsm
	B	C	D	E	F
1	Number Kgal	100.00
2	Amount	93.20	93.20	93.20	93.20
3
4	Units	Rates	Rate Diff
5	blank row
6	0	0.80	0.80
7	6	0.85	0.05
8	12	0.90	0.05
9	18	0.95	0.05
7a

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT(--(C1>=B6:B9),--(C1-B6:B9),D6:D9)
D2	D2	=SUMPRODUCT(--(C1>B6:B9),--(C1-B6:B9),(C6:C9-C5:C8))
E2	E2	=SUMPRODUCT(--(C1>{0;6;12;18}),--(C1-{0;6;12;18}),{0.8;0.05;0.05;0.05})
F2	F2	=(C1>0)*C1*0.8+(C1>6)*(C1-6)*0.05+(C1>12)*(C1-12)*0.05+(C1>18)*(C1-18)*0.05
D6	D6	=C6-N(C4)
D7:D9	D7	=C7-N(C6)

EDIT, I figured it out. Thanks!

 

[EvanBerrett]

@EvanBerrett
Please take the time to read our rules & in future abide by them
Also posted here Water Billing Rates Formula [SOLVED]

Have you posted this anywhere else?

Also poster here: Water Billing Rates Formula - OzGrid Free Excel/VBA Help Forum. That is all.

 

[Fluff]

Many thanks. In future please supply the links in the op.

 

[EvanBerrett]

Many thanks. In future please supply the links in the op.

Understood.

 

[Fluff]