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.

  • Hi EvanBerrett, welcome to OzGrid.


    Please update your post with links to the other forums you have asked this question. If you are unable to do so please ask myself or any of the moderators to amend the post for you with the necessary links.


    For more information on this request - please refer to the forum rules (in my signature) which explain what cross-posting is and why we ask you to supply links.


    Many thanks


    Sam