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