×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel Formula For Utility Water Billing

Excel Formula For Utility Water Billing

Excel Formula For Utility Water Billing

(OP)
Our city utility water rates are outrageous and I am trying to do the formulas to calculate the bill and then compare them to historic bills and water rates from other towns.
1st 2,000 gallons are $21.61, after that the rates are tiered, 2001 to 10,000 gallons are $6.11 etc.
I used 21,000 gallons water and total bill with sewer and garbage is $205.04.
My formula for E7 is: =IF($E$2-2=1,1*D7,IF($E$2-2=2,2*D7,IF($E$2-2=3,3*D7,IF($E$2-2=4,4*D7,IF($E$2-2=5,5*D7,IF($E$2-2=6,6*D7,IF($E$2-2=7,7*D7,IF($E$2-2=8,8*D7,IF($E$2-2=9,9*D7,IF($E$2-2>=10,8*$D$7,0))))))))))
Isn't there a better way to do this formula? If I get an example I should be able to the the other rows, I don't use Excel formulas as much in the last several years and I can't remember how best to do this.



RE: Excel Formula For Utility Water Billing

Hi,

I've uploaded a workbook. Your picture made work for me. 🥵

You'll have to look at the formulas. You tried to make it more difficult than need be.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Formula For Utility Water Billing

(OP)
Skip, thank you very much! I really struggled with getting these formulas right so I did it the long way. I have realized I am way behind on my Excel formulas. When I joined here in 2002, I was a lot better but time flies and I moved on to other careers. This really helps, thanks!

RE: Excel Formula For Utility Water Billing

Hey, gotta take care of a fellow Texan.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Formula For Utility Water Billing

Classic Excel, when you could solve something with one formula, but end up creating a whole page with numbers hahahaha

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close