Formula for billing water

[Guest]

Would someone please tell me the formula for billing say 5,000 gallons of
water with the first 3,000 at .075 and the remainder at .110? Thank you.

 

[Paul B]

Flow, with 5000 in A1, here is one way,
=IF(A1<=3000,A1*0.075,3000*0.075+(A1-3000)*0.11)

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Peo Sjoblom]

One way

=MIN(A1,3000)*0.75+((A1-3000)*0.11)

--
Regards,

Peo Sjoblom

Portland, Oregon

 

[Dana DeLouis]

with the first 3,000 at .075 and the remainder at .110?

I may be wrong, but I notice that the price increases with use. Is there an
error? ( Op forgot to include units)
Perhaps another option...

=MAX(0.075*A1,0.11*A1-105)

 

[Peo Sjoblom]

Yep you are right of course, I read it as 0.75 which of course is insane
<bg>

--
Regards,

Peo Sjoblom

Portland, Oregon

 

[flummi]

Here's another way:

table:

Qty from 0 1000 2000 3000
Qty to 1999 1999 2999 above
price 1,25 1,11 0,97 0,75

data:

consumption price total price
2500 0,97 2425,00
800 1,25 1000,00
1200 1,11 1332,00
2000 0,97 1940,00
1000 1,11 1110,00
3001 0,75 2250,75
2999 0,97 2909,03
5500 0,75 4125,00

B3:E5 holds a pricing table.
A10:A17 is your consumption data.
B10:B17 is your price/qty from the pricing table.
D1017 is yor total price.

Formula in B10 (copied down to B17):
=HLOOKUP($A10;$B$3:$F$7;3)

Formula in D10 (copied down to D17):
=A10*B10

Hope this helps.

Hans

Sorry for the crap formatting

 

[Dana DeLouis]

Hi Peo. :>) What I "meant" to say was that I was expecting to use a Min
function, thinking that prices usually drop with more purchase. However,
above 3,000 (gallons I assume), the price increased from 7.5¢ to 11¢.
(Therefore a Max function).
I was just wondering/guessing if the op had the numbers backwards. I
suppose though that there could be a "penalty" for water use above 3,000. I
was just curious. :>)