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.

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" <Flow@discussions.microsoft.com> wrote in message
news:EB7DDAED-FC72-45C8-B9A3-264D5CD5D8A3@microsoft.com...
> 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.

One way

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

--
Regards,

Peo Sjoblom

Portland, Oregon




"Flow" <Flow@discussions.microsoft.com> wrote in message
news:EB7DDAED-FC72-45C8-B9A3-264D5CD5D8A3@microsoft.com...
> 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.

> 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" <terre08@mvps.org> wrote in message
news:%23krMvdeKGHA.916@TK2MSFTNGP10.phx.gbl...
> One way
>
> =MIN(A1,3000)*0.75+((A1-3000)*0.11)
>
> --
> Regards,
>
> Peo Sjoblom
>
> Portland, Oregon
>
>
>
>
> "Flow" <Flow@discussions.microsoft.com> wrote in message
> news:EB7DDAED-FC72-45C8-B9A3-264D5CD5D8A3@microsoft.com...
>> 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.
>

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" <ddelouis@bellsouth.net> wrote in message
news:Osz1kneKGHA.1320@TK2MSFTNGP15.phx.gbl...
>> 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" <terre08@mvps.org> wrote in message
> news:%23krMvdeKGHA.916@TK2MSFTNGP10.phx.gbl...
>> One way
>>
>> =MIN(A1,3000)*0.75+((A1-3000)*0.11)
>>
>> --
>> Regards,
>>
>> Peo Sjoblom
>>
>> Portland, Oregon
>>
>>
>>
>>
>> "Flow" <Flow@discussions.microsoft.com> wrote in message
>> news:EB7DDAED-FC72-45C8-B9A3-264D5CD5D8A3@microsoft.com...
>>> 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.
>>
>
>

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

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>