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.

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
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,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
RE: Excel Formula For Utility Water Billing
RE: Excel Formula For Utility Water Billing
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
https://www.ymail.info/
RE: Excel Formula For Utility Water Billing