Water Billing Rates Formula

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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

    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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login