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.

 

[etaf]

this may help

McGimpsey & Associates : Excel : Using SUMPRODUCT() with variable rates

 

[EvanBerrett]

this may help

McGimpsey & Associates : Excel : Using SUMPRODUCT() with variable rates

Sorry, I'm not quite knowledgeable enough to know how to translate to my case.

 

[etaf]

ok, we can do a nested IF
what happens at 6Kg , 12 , 18 is it the lower of higher value as you have those listed twice

 

[EvanBerrett]

ok, we can do a nested IF
what happens at 6Kg , 12 , 18 is it the lower of higher value as you have those listed twice

First 6 Kgal is $0.80, so after 6 Kgal, the cost goes up to $0.85

 

[etaf]

=IF(A2<=6,A2*0.8,IF(A2<=12,(6*0.8)+((A2-6)*0.85),IF(A2<=18,((6*0.8)+(6*0.85)+((A2-12)*0.9)),((6*0.8)+(6*0.85)+(6*0.9)+((A2-18)*0.95)))))
does this work for you ?

 

[Fluff]

Cross posted Water Billing Rates Formula - OzGrid Free Excel/VBA Help Forum

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[EvanBerrett]

=IF(A2<=6,A2*0.8,IF(A2<=12,(6*0.8)+((A2-6)*0.85),IF(A2<=18,((6*0.8)+(6*0.85)+((A2-12)*0.9)),((6*0.8)+(6*0.85)+(6*0.9)+((A2-18)*0.95)))))
does this work for you ?

It does, thank you very much

 

[Dave Patton]

Three versions of the SumProduct formula.
Try each or the version that you prefer.

T202003a.xlsm
	B	C	D	E
1	Number Kgal	200.00
2	Amount	188.20	188.20	188.20
3
4	Units	Rates	Rate Diff
5
6	0	0.80	0.80
7	6	0.85	0.05
8	12	0.90	0.05
9	18	0.95	0.05
10
11	188.2
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>B6:B9),--(C1-B6:B9),{0.8;0.05;0.05;0.05})
D6	D6	=C6-N(C4)
D7:D9	D7	=C7-N(C6)
B11	B11	=6*0.8+6*0.85+6*0.9+182*0.95

D2 does not require D4:D9
E2 does not require the Rate Table the formula should be =SUMPRODUCT(--(C1>{0;6;12;18}),--(C1-{0;6;12;18}),{0.8;0.05;0.05;0.05})

Arithmetic
=(A2>0)*A2*0.8+(A2>6)*(A2-6)*0.05+(A2>12)*(A2-12)*0.05+(A2>18)*(A2-18)*0.05

 

[EvanBerrett]

Three versions of the SumProduct formula.
Try each or the version that you prefer.

T202003a.xlsm
	B	C	D	E
1	Number Kgal	200.00
2	Amount	188.20	188.20	188.20
3
4	Units	Rates	Rate Diff
5
6	0	0.80	0.80
7	6	0.85	0.05
8	12	0.90	0.05
9	18	0.95	0.05
10
11	188.2
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>B6:B9),--(C1-B6:B9),{0.8;0.05;0.05;0.05})
D6	D6	=C6-N(C4)
D7:D9	D7	=C7-N(C6)
B11	B11	=6*0.8+6*0.85+6*0.9+182*0.95

D2 does not require D4:D9
E2 does not require the Rate Table the formula should be =SUMPRODUCT(--(C1>{0;6;12;18}),--(C1-{0;6;12;18}),{0.8;0.05;0.05;0.05})

Arithmetic
=(A2>0)*A2*0.8+(A2>6)*(A2-6)*0.05+(A2>12)*(A2-12)*0.05+(A2>18)*(A2-18)*0.05

This is fantastic, thank you!