End up with the same totals -it is not adding the differnce of each calculation
example:
if a consumption is 22000
per = 99.75
additonal
should be a total of 105.00

formula produces 115.50

"Bernard Liengme" wrote:

Yet another
=40*(A1<8501)+(A1/1000)*((A18500)*4.75 +(A120099)*0.5+(A150000)*0.75)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"SM_NCSW" wrote in message
...
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?

Try this:
=IF(A24<8501,40,ROUND((MIN(A24,21000)*4.75+MIN(290 00,MAX(0,A24-21000))*5.25+MAX(0,A24-50000)*6)/1000,2)

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"SM_NCSW" wrote in message
...
End up with the same totals -it is not adding the differnce of each
calculation
example:
if a consumption is 22000
per = 99.75
additonal
should be a total of 105.00

formula produces 115.50

"Bernard Liengme" wrote:

Yet another
=40*(A1<8501)+(A1/1000)*((A18500)*4.75 +(A120099)*0.5+(A150000)*0.75)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"SM_NCSW" wrote in message
...
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?