Utility Meter Reading Formulas

[mtbe]

I volunteer for a water utility system in the mountains of Colombia, S America. All of us are volunteers. It's community of about 190 houses/farms that gets water from a mountain stream.

The government has asked us to install water meters on each house, which we are doing now. But, they don't help with setting anything up for recording or billing.

I've created an Excel spreadsheet to capture the current readings and subtract the previous reading to calculate the consumption, but I'm not sure how to roll the following concerns into the formula.

• The homes are spread out and some are very difficult to access in the mountains. Some of the clients we will be unable to read each month. I know I can check if the current reading is blank, to check the reading from 2 months ago, and calculate an average consumption from the previous two readings. But what if it's been 2, 3 or more months since we've been able to do a reading?
  
  
• Due to the difficulty in reading some of these each month, we also may have to use the average of the other homes in the area.
  
  
• Due to the difficulty in accessing some of the homes, we may not be able to read the meters exactly every 30-31 days. Some months might be 25 days, or 35 days. Is the billing supposed to calculate for 30 or 31 days (depending on the month), so if the consumption for the month was 3500 liters and was read 35 days after the previous reading, would the bill be shown as 3000 liters for 30 days of the month?
  
  
• How is rollover of the meter handled? Since these are new meters, it will be several years before we get a rollover, but I'd like to add that capability into the formula. So, if the previous month was 9999.999, and the current month reading is 0001.888, how is that handled in the formula?

Any help would be greatly appreciated. This is a poor community but we are trying to teach them about conservation of water.

 

[Michael M]

I'd suggest either uploading the workbook to a hosting site, like dropbox, so we can see the layout and post a link to that site back here.
Failing that, use the XL2BB addin to upload the layout of the workbook back here.
the XL2BB addin is in my tag.
I would always try to provide a daily usage either by averages or by direct calculation, as you can use that figure for all future calculations, if needd.

 

[mtbe]

Thanks for the suggestion. I have an Excel form in the team's Google Drive, which is linked below. I keep it in Google Drive as it's a shared location for the team. This is a copy of what I have, not the original.

Meter Reading Spreadsheet

On the Lecturas tab, the rows 46 and higher have the information from the meter readings. I created an app that the field person can enter the meter reading, and the app saves it (and calculates the consumption) in this Excel sheet.

But, I'm stuck on those bullet points in my original post...

 

[Tetra201]

The rollover of the meter is handled like this:

=MOD(1.888-9999.999,10000)

 

[mtbe]

Sorry...can't figure out how to edit my post:

My Excel spreadsheet only shows the meters that have readings. It doesn't show those meters for the month that don't have readings. I'm still trying to filter those out, and apply the formulas that I need to calculate the 'estimated' consumption.