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

A1 is how many gallons
therefore, yoru formula would be
=A1*LOOKUP(A1,{0,?;5000,1;15000,1.5;25000,1.75})
The ? is because what happens if the it is under 5000 gallons? I understand
the argument of "well, that's never happened", but still, like to be
prepared. If the rate is still 1, then you could change the part between the
curly-cue brackets thusly
{0,1;15000,1.5;25000,1.75}

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


"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

John,

Thanks for the help. I'll give it a try. As for the under 5,000 gallons,
that will be charged at the basic rate of $18.50.

Thanks again.

"John C" wrote:

A1 is how many gallons
therefore, yoru formula would be
=A1*LOOKUP(A1,{0,?;5000,1;15000,1.5;25000,1.75})
The ? is because what happens if the it is under 5000 gallons? I understand
the argument of "well, that's never happened", but still, like to be
prepared. If the rate is still 1, then you could change the part between the
curly-cue brackets thusly
{0,1;15000,1.5;25000,1.75}

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


"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

Hi

One way, with gallons in cell A1
=(A1*1+MAX(0,A1-15000)*0.5+MAX(0,A1-25000)*0.25)/1000

--
Regards
Roger Govier

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

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

The /1000 that I didn't include, but you did, I added it, and I come up with
$112.452
=(A1/1000)*LOOKUP(A1,{0,18.5;5000,1;15000,1.5;25000,1.7 5})

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


"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

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

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

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

Johm,

That works. That is a big help. Thank you. Now to make sure I don't screw
it up, how would I change it to have the same gallon scale, but change the
rate from $1 to $0.75, $1.50 to $1.25, and $1.75 to $1.50?

Again, thanks for the help.

JP

"John C" wrote:

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

Hi
then change my formula to
=10+(A125000)*15+(INT(MAX(0,A11-25000)/1000)*1.75)

--
Regards
Roger Govier

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