Excel Formula For Utility Water Billing 1

[esb6]

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.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[esb6]

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!

 

[SkipVought]

Hey, gotta take care of a fellow Texan.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[miguelaraujo]

thank you very much!

https://www.ymail.info/

 

[LukeyLuke]

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