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!!
