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?

=IF(A1<0,"",IF(A1<8501,40,IF(A1<21000,A1*4.75/1000,IF(A1<50001,A1*5.25/1000,A1*6/1000))))
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?

See

http://www.mcgimpsey.com/excel/variablerate.html

for one way.


In article ,
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?

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?

Here is a mix of my first reply and John's use of LOOKUP
=IF(A1=0,"",IF(A1<8501,40,A1/1000*LOOKUP(A1,{8501,4.75;21000,5.25;50001,6})))
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?

This does function--however it is returning incorrect values--for instance
22000 gallons should = $105 which is 21000@ 4.75 the 1000 gallons over at
5.25per

"Bernard Liengme" wrote:

=IF(A1<0,"",IF(A1<8501,40,IF(A1<21000,A1*4.75/1000,IF(A1<50001,A1*5.25/1000,A1*6/1000))))
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?

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?

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

formula is producing 115.50

"Bernard Liengme" wrote:

Here is a mix of my first reply and John's use of LOOKUP
=IF(A1=0,"",IF(A1<8501,40,A1/1000*LOOKUP(A1,{8501,4.75;21000,5.25;50001,6})))
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?

Heck, then it is exactly like a question I answered just this past week.
You can go to the general questions group, and search for topics with these
two identifiers: stateofdenial gallons.
This is the link for the actual question and follow up answers.
http://www.microsoft.com/office/comm...e-18e01af81aa8

--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"SM_NCSW" wrote:

This does function--however it is returning incorrect values--for instance
22000 gallons should = $105 which is 21000@ 4.75 the 1000 gallons over at
5.25per

"Bernard Liengme" wrote:

=IF(A1<0,"",IF(A1<8501,40,IF(A1<21000,A1*4.75/1000,IF(A1<50001,A1*5.25/1000,A1*6/1000))))
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?

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?

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?

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?

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?

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?

John

=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)
this formula works except for one thing
anything above 8500 but below 21000 it is adding the $40 to the total--once
the 8500 is exceeded the 4.75 rate kicks in from zero up to the 21000


"John C" wrote:

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?

Yeah, I caught that on my post. The one you copied still shows the +40, but
when I posted back, it doesn't have the +40 anymore.
=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)

--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"SM_NCSW" wrote:

John

=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)
this formula works except for one thing
anything above 8500 but below 21000 it is adding the $40 to the total--once
the 8500 is exceeded the 4.75 rate kicks in from zero up to the 21000


"John C" wrote:

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?

John
Thank you so much--this works great!

"John C" wrote:

Yeah, I caught that on my post. The one you copied still shows the +40, but
when I posted back, it doesn't have the +40 anymore.
=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)

--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"SM_NCSW" wrote:

John

=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)
this formula works except for one thing
anything above 8500 but below 21000 it is adding the $40 to the total--once
the 8500 is exceeded the 4.75 rate kicks in from zero up to the 21000


"John C" wrote:

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?

Thanks for the feedback.
--
** John C **


"SM_NCSW" wrote:

John
Thank you so much--this works great!

"John C" wrote:

Yeah, I caught that on my post. The one you copied still shows the +40, but
when I posted back, it doesn't have the +40 anymore.
=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)

--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"SM_NCSW" wrote:

John

=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)
this formula works except for one thing
anything above 8500 but below 21000 it is adding the $40 to the total--once
the 8500 is exceeded the 4.75 rate kicks in from zero up to the 21000


"John C" wrote:

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?

SM_NCSW wrote:
John

=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)
this formula works except for one thing
anything above 8500 but below 21000 it is adding the $40 to the total--once
the 8500 is exceeded the 4.75 rate kicks in from zero up to the 21000




=IF(A1<8500,40,MIN(A1,20999)*0.00475+MAX(A1-20999,0)*0.00525+MAX(A1-49999,0)*0.006)

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?

On Fri, 24 Oct 2008 11:34:04 -0700, 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?

You have some undefined amounts and inconsistencies.

For example, 8500-8501
20999-21000

But the following seems to do what you want. You can modify some of the
breakpoints if necessary.

Set up a 3 column table someplace on your sheet as follows and NAME it
RateTable:

0 $0.00000 $0.00475
8500 $40.37500 $0.00475
21000 $99.75000 $0.00525
50000 $252.00000 $0.00600


Then use this formula:

=IF(gals<=8500,40,VLOOKUP(gals,RateTbl,2)+
VLOOKUP(gals,RateTbl,3)*(gals-VLOOKUP(gals,RateTbl,1)))

--ron

On Fri, 24 Oct 2008 11:34:04 -0700, 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?


That table should have been NAME'd RateTbl
--ron

Ron,
Thank you--this works!
However, I am trying to figure out how the formula is using the table in the
calculation (rows or columns)--I may be able to use this type of calculation
for other functions. I am new to more than just your basic calculations,
your insite is most appreciated.


"Ron Rosenfeld" wrote:

On Fri, 24 Oct 2008 11:34:04 -0700, 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?


That table should have been NAME'd RateTbl
--ron

On Tue, 28 Oct 2008 08:31:04 -0700, SM_NCSW
wrote:

Ron,
Thank you--this works!
However, I am trying to figure out how the formula is using the table in the
calculation (rows or columns)--I may be able to use this type of calculation
for other functions. I am new to more than just your basic calculations,
your insite is most appreciated.

Take a look at HELP for VLOOKUP (and HLOOKUP for completeness).

Then use the Formula Evaluation tool to see what is going on, step-by-step.

Basically, in this table, column 1 contains the "break points", column 2 is the
total for up to that break point, and column 3 is the multiplier for charges
over that breakpoint.

0 $0.00000 $0.00475
8500 $40.37500 $0.00475
21000 $99.75000 $0.00525
50000 $252.00000 $0.00600


In your case, a special case needs to be made for up to 8500 since you later
indicated that amounts over 8500 would have the rate of 4.75 applied to the
entire amount, but 8500 would be charged only $40, instead of $40.375

So, if you have 22000 gallons:

=IF(gals<=8500,40,
This evaluates TO False, so go to next step

VLOOKUP(gals,RateTbl,2)
Get charges for up to 21000 = 99.75

+VLOOKUP(gals,RateTbl,3)
Get multiplier for charges above 21000 = $0.00525/gal

*(gals-VLOOKUP(gals,RateTbl,1)))
Get number of gallons above 21000 = 1000
Multiplied by $0.00525 = $5.25

Add to the $99.75 from step 2 = $105




--ron