Formula for billing water
 

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.

Formula for billing water
 

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

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

Formula for billing water
 

One way

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

--
Regards,

Peo Sjoblom

Portland, Oregon




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

Formula for billing water
 

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)

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"Peo Sjoblom" wrote in message
...
One way

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

--
Regards,

Peo Sjoblom

Portland, Oregon




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

Formula for billing water
 

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

--
Regards,

Peo Sjoblom

Portland, Oregon




"Dana DeLouis" wrote in message
...
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)

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"Peo Sjoblom" wrote in message
...
One way

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

--
Regards,

Peo Sjoblom

Portland, Oregon




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

Formula for billing water
 

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.
D10:D17 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

Formula for billing water
 

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. :)

--
Dana

<snip