×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

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

thread68-1804667

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