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
A: 0 - 6 Kgal
B: 6 - 12 Kgal
C: 12 - 18 Kgal
D: 18+ 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.