Well, again assuming you bill in 1000 gallon increments, then the formula I
gave you should work
=CHOOSE(LOOKUP(A1,{0,1;8501,2;21000,3;50001,4}),40 ,INT(A1/1000)*4.75,INT((A1-21000)/1000)*5.25+99.75,INT((A1-50000)/1000)*6+99.75+152.25)

I broke down how my formula works (see link in previous post), but I will
give you a brief rundown.
4 Categories are possible.
This is why I used the CHOOSE/LOOKUP combination. So the four resultant
formulas a
40 ..... this is just the flat rate if under 8500
INT(A1/1000)*4.75 ..... this is the charge for 8501-21000
INT((A1-21000)/1000)*5.25+99.75 ..... this is the charge for 21001-50000
plus the 99.75 for the first 21000
INT((A1-50000)/1000)*6+99.75+152.25 ..... this is the charge for 50000+ plus
the 99.75 for the first 21000 plus 152.25 for the next 29000

--
** John C **


"SM_NCSW" wrote:

John
I'm sorry please let me clarify. If a account uses up to 8500 it is an
automatic min. charge of $40, if they use 0-20999 they would be billed
@4.75/1000, /1000,50000@6/1000. The problem is the adding
the differnces together. Example if they used 52000 they would be billed 4.75
the first 21K plus plus 2K@6

"John C" wrote:

Are you sure you are accurate?
You gave us information saying <8500 = 40
8501-21000 = 4.75/1000. I think this is where the error lies. Are you saying
that if they are between 8500 and 21000, then it is 4.75/1000 from gallon 0
to 15000(ex?). Or is it a flat 40 for the first 8500, and then 4.75/1000
between 8500 and 21000. Your other response states that it is 4.75/1000 for
21000 gallons = 99.75, but this would cover the first 8500 as well. Assuming
that is what you are wanting, and then overages over 21000 are at 5.25/1000
and overages over 50000 are 6/1000, then perhaps this formula will work.
=CHOOSE(LOOKUP(A1,{0,1;8501,2;21000,3;50001,4}),40 ,INT(A1/1000)*4.75+40,INT((A1-21000)/1000)*5.25+99.75,INT((A1-50000)/1000)*6+99.75+152.25)
And this also only bills if full 1000 gallon increments.
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"SM_NCSW" wrote:

The formula does function but it is not adding the differnce of the rates
Example:
if a consumption is 22000
per = 99.75
additonal
total of 105.00

formula is producing 115.50


"John C" wrote:

I just posted this formula for a similar type question. With a similar
formula, how about this?

=CHOOSE(LOOKUP(A1,{0,1;8501,2;21000,3;50001,4}),40 ,A1*4.75/1000,A1*5.25/1000,A1*5.25/1000)

I think these are the calculations you want. If not, I think you can follow
the pattern, otherwise, post back, and give sample expectations.
--
** John C **


"SM_NCSW" wrote:

I am trying to calculate a progressive rate for water use charges:
<8500=$40,
8501 up to /1000
21000 up to /1000
50000 @6.00/1000

Col.A lists consumtion in gallons
is there a formula I can use for this?