Formula for calculating bills using multi-tier rates

[w2w]

I need some help on a formula for calculating customers bills for water usage. The rate structure is graduated and is beyond my abilities. Basically I have a spreadsheet where customers account information including their usage (in gallons) for a given month in Column F. The rate structure is as follows:
$11.50 min Includes 2000 gallons
$2.52 per thousand for the next 3000 gallons
$2.12 per thousand for the next 5000 gallons
$1.37 per thousand for anything over 10000 gallons.

I would like to have all of the rates in a legend at the top so that I can experiment with different rates by simply entering them into that cell and be able to see the effects it would have on that months billing.

Thanks

 

[Jonmo1]

Welcome to the board..

See if this helps..
http://www.officearticles.com/excel/calculate_tiered_commissions_in_microsoft_excel.htm

Don't worry that it's talking about commissions..
The math is still the same

 

[Asala42]

Here's a couple variants if you're looking for a single big formula:

Nested ifs
=IF(E1<=2000,11.5,IF(E1<=5000,11.5+(E1-2000)*2.52/1000,IF(E1<=10000,11.5+7.56+(E1-5000)*2.12/1000,11.5+7.56+10.6+(E1-10000)*1.37/1000)))

No ifs
=(E1<=2000)*11.5+(E1<=5000)*(E1>2000)*(11.5+(E1-2000)*2.52/1000)+(E1<=10000)*(E1>5000)*(11.5+7.56+(E1-5000)*2.12/1000)+(E1>10000)*(11.5+7.56+10.6+(E1-10000)*1.37/1000)