×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• 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.

# Excel Formula For Utility Water Billing

 Forum Search FAQs Links MVPs

## 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,

Just traded in my OLD subtlety...
for a NUance!

"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,

Just traded in my OLD subtlety...
for a NUance!

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

### RE: Excel Formula For Utility Water Billing

thank you very much!

### 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.

#### 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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!