Forum Discussion
calculating water rates on a per thousand/tier
I am trying to calculate a water table that I could change the rates in B12-B23 in order to get a monthly bill about for water consumption. I have found several issues.
Even if 0 qty is used there still is a charge of 7.50 I need to know what to put in the formula to have 7.50 put in even if 0 qty of water is used.
Qtys 0-3 do not have a monthly 7.50 charge so all other amounts above that need to have 7.50 added to the bill.
I need my equation to charge the monthly fee if applicable, and the cost per thousand.
example in B13 the qty used was 2 (2000 gallons of water) that water is charged at 7.50 flat. Because 0-3 qty has a flat rate of 7.50. But on B14 the formula should show that the customer is being billed 7.50 for the 1st 3k gallons of water, and then needs to be billed 3.19 for the extra thousand gallons of water. Each thousand gallons of water used needs to be billed based on the water sales. Does this make sense? attached is my spread sheet. Please HELP!!
Hi Alexzandra,
If you define your ranges like this
the formula could be
=MAX(B12*LOOKUP(B12,$F$3:$F$8,$G$3:$G$8)+LOOKUP(B12,$F$3:$F$8,$H$3:$H$8),$G$3)
and attached
To calculate your water rates in Excel, use this formula for each cell in column B: =IF(A13=0, 7.50, 7.50 + (A13-3)*3.19) where A13 is the water quantity in thousands of gallons. This formula ensures that if the quantity is 0, the charge is still $7.50. For quantities between 0 and 3 thousand gallons, the charge remains $7.50. For amounts above 3 thousand gallons, it adds $3.19 per extra thousand gallons. This way, you can adjust your rates easily. If you're looking for a reliable water purifier, consider the olansi water purifier to ensure clean water at home.
