Formula Function

[SM_NCSW]

I am trying to calculate a progressive rate for water use charges:
<8500=$40,
8501 up to [email protected]/1000
21000 up to [email protected]/1000

50000 @6.00/1000

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

 

[Bernard Liengme]

=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

 

[JE McGimpsey]

See

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

for one way.

 

[John C]

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.

 

[Bernard Liengme]

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

 

[SM_NCSW]

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]

Yet another
=40*(A1<8501)+(A1/1000)*((A1>8500)*4.75 +(A1>20099)*0.5+(A1>50000)*0.75)
best wishes

 

[SM_NCSW]

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

formula is producing 115.50

 

[John C]

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/com...3e27&mid=f7ad3454-b1af-45c2-8b7e-18e01af81aa8

 

[SM_NCSW]

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

formula produces 115.50

 

[SM_NCSW]

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

formula is producing 115.50

 

[John C]

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.

 

[SM_NCSW]

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, [email protected]/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 [email protected] plus 2K@6

 

[John C]

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 are:
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

 

[SM_NCSW]

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]

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)

 

[SM_NCSW]

John
Thank you so much--this works great!

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]

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

John
Thank you so much--this works great!

 

[Glenn]

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)

 

[Bernard Liengme]

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