# Posts by EvanBerrett

• ## Water Billing Rates Formula

I apologize. I was looking for a quick response. I don't have the ability to change the post, but I did receive a solution, so you can close this thread anyway.

• ## Water Billing Rates Formula

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 helpful. I have attached an excel spreadsheet to give more clarity.