Okay, I understand now, and I know why both his and mine were incorrect. Here
is something you should be able to use:
=CHOOSE(LOOKUP(A1,{0,1;5000,2;15000,3;25000,4}),0, INT((A1-5000)/1000),INT((A1-15000)/1000)*1.5+10,INT((A1-25000)/1000)*1.75+25)
This implies that they won't be charged the first $1.00 overage until they
actually reach 6000 gallons; so, $2 @ 7k, $3@8k, $4@9k, $5@10k..., etc.
Also, it also implies that you are ONLY charging for whole increments of
1000, so if they had usage of 5999, then no charge. If they are supposed to
have partial charges, (i.e.: $0.99 for 5990), then just remove the INTs from
the formula:
=CHOOSE(LOOKUP(A1,{0,1;5000,2;15000,3;25000,4}),0, ((A1-5000)/1000),((A1-15000)/1000)*1.5+10,((A1-25000)/1000)*1.75+25)



--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"Rhomium" wrote:

John,

Sorry about that. Also I wrote that wrong, the $93.25 is just the overage
after the basic fee. The way it will be billed is: $10 for the first
15,000, plus $15 from 15-25,000, plus everything over that at $1.75 per 1000
for a total of $93.25.


Thanks again, appreciate the help.

"John C" wrote:

I guess I should have asked how do you come up with 93.50. You stated that
the rate for per 1000gallon if they exceed 25000 is 1.75. Well, 1.75 * 64000
is 112 even, the .45 is obviously because you have above 64000 gallons. Do
you only bill in whole gallon increments?

Again, though, please clarify how 93.50 is accurate.
--
** John C **



"Rhomium" wrote:

John: With your formula on 64258 gallons, the result is $112.45, it should
be $93.50

Roger: With your formula on 64258 gallons, the result is $98.70, it should
be $93.50.

Any ideas on what the problem is?

"Rhomium" wrote:

Hello,

I'm trying to calculate water overage rates for my Town and could use some
help with a formula.

A cell contains the number 64258(gallons). For gallons between 5,000 and
15,000, the rate is $1 per 1,000 gallons (10*1), between 15,000 and 25,000
rate is $1.50 per 1000 gallons (10*1.50), and any over 25,000 rate is $1.75
per 1,000.

Anyone have any idea how to write the formula?

Thanks for any help.

JP