Help w/ formula

[Rhomium]

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 C]

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}

 

[Roger Govier]

Hi

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

 

[Rhomium]

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.

 

[Rhomium]

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?

 

[John C]

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.75})

 

[John C]

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.

 

[Rhomium]

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]

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)

 

[Rhomium]

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]

I guess my formula should have been like thi
=CHOOSE(LOOKUP(A1,{0,1;5000,2;15000,3;25000,4}),0,INT((A1-5000)/1000)*1,INT((A1-15000)/1000)*1.5+10,INT((A1-25000)/1000)*1.75+25)

The *1 was added to show where the $1.00 rate is, the others are easy to
understand. I will explain what the formula is doing, and I think that should
help you out a bit.
First, I broke the gallons up into 4 categories, 0, 5000, 15000, and 25000,
then I assigned a category number to these of 1, 2, 3, 4. This is done by the
LOOKUP. 0-4999 = category 1, 5000-14999 = category 2, 15000-24999 = category
3, and 25000+ = category 4.
The choose function works like this:
=CHOOSE(INDEX,choice1,choice2,choice3,choice4). It must have at least 2
arguments, the INDEX, and the first choice. The index needs to be a whole
number, starting with 1, which is why I did the categories 1-4 as above.
Then, I needed formulas to calculate the overage for each category.
Category 1 is the first argument after the lookup, is 0, since obviously
they had no overage, therefore there will be no overage charge.
Category 2 is the second argument after the lookup:
INT((A1-5000)/1000)*1
This will take the total amount of gallons-5000 that won't be charged as
overage, then divides by the 1000. Since you are billing for only 1000 gallon
increments, I needed the INTeger function, then I multiply by the rate (1).
Category 3 is the 3rd argument after the lookup:
INT((A1-15000)/1000)*1.5+10
Same thought process here, calculate the total number of 1000 gallon
increments that fall into this category and multiply by its category rate
(1.5), but I have to remember what the total charge for the gallons from
category 1, which is represented by the +10 (10*1)
Category 4 is the 4th (and final) argument after the lookup:
INT((A1-25000)/1000)*1.75+25
Identical to thought process for Category 3, calculating the total number of
1000 gallon increments that fall into this category multiplied by its
category rate (1.75), and adding the total charge for all 1000 gallon
increments for categories 2 & 3 which is represented by the +25 (10*1 + 1.5*1)

So, to answer your question, lol, you could make your formula like this
=CHOOSE(LOOKUP(A1,{0,1;5000,2;15000,3;25000,4}),0,INT((A1-5000)/1000)*0.75,INT((A1-15000)/1000)*1.25+7.50,INT((A1-25000)/1000)*1.50+20)

 

[Rhomium]

John,

Thanks again. That should cover everything.

JP

 

[John C]

Please remember to check the YES box below, it helps people know a question
has been resolved. And thank you for the feedback

 

[Roger Govier]

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