Welcome! Here are the website rules, as well as some tips for using this forum.
Need to contact us? Visit https://heatinghelp.com/contactus/.
Click here to Find a Contractor in your area.
Excel Guru?
Zman
Member Posts: 7,370
I am in the process of building a spread sheet to help track energy usage for a client with a very complex network of meters.
The problem I am having is how to deal with their sub meters when they eventually rollover. I have a column that shows how many digits each meter has, I just can't figure out how to make it automatically deal with this inevitability.
@Boon , if I recall correctly, this might be up your alley.
Thanks you in advance,
Carl
The problem I am having is how to deal with their sub meters when they eventually rollover. I have a column that shows how many digits each meter has, I just can't figure out how to make it automatically deal with this inevitability.
@Boon , if I recall correctly, this might be up your alley.
Thanks you in advance,
Carl
"If you can't explain it simply, you don't understand it well enough"
Albert Einstein
Albert Einstein
0
Comments

Assuming it won't roll over twice before you record the reading, why not just append a number in the beginning?
IOW, when it goes from 999999 to 000001, it should be recorded as 100001. Then you could keep a lifetime total. Of course all new readings now have to included the appended number.
You could also create a formula to subtract, depending on the setup. The logic would be, assuming a 6 digit number:
If NewReading < OldReading //a rollover
NewReading + 1000000  OldReading
otherwise NewReading OldReading
steve0 
Steve,
The idea would be that the spread sheet would be totally bullet proof and never need to be altered. An employee enters the data from the meters and the bookkeeper sends out the bills based on the info the sheet spits out.
I suspect that the solution is some sort of if/then equation where if the old number starts with a nine and the new starts with a zero then a formula applies.
I do have a column that gives the digits for each meters.
Thanks for your input.
"If you can't explain it simply, you don't understand it well enough"
Albert Einstein0 
Let me play around with it. If I come up with something I'll have to email it to you if I can't post it here.steve0

For scalability and simplicity the first thing that comes to mind is having the employee make multiple entries when the meter flips. See attached. This is the least amount of logic, all the formulas are short and sweet, and the situation is accurately represented. The employee can enter dates and meter readings all the way to the bottom of columns A and C without any changes to the spreadsheet. Usage is calculated when a billing date is entered.
If this isn't quite right I can work on something else. The trouble with other solutions is transparency & complexity.
DavidDIY'er ... ripped out a perfectly good forcedair furnace and replaced it with hot water & radiators.0 
Here ya go. PM me if you need instructions
Hopefully it will load.
Truth be told, it would be much better on a databasemore functions, more options.
But let me know if it needs any tweakssteve1 

The simple calculation is to have a field for the number of digits a meter contains. So a four digit meter has a cell (let's say B2) with the number 4.Boon said:For scalability and simplicity the first thing that comes to mind is having the employee make multiple entries when the meter flips. See attached. This is the least amount of logic, all the formulas are short and sweet, and the situation is accurately represented. The employee can enter dates and meter readings all the way to the bottom of columns A and C without any changes to the spreadsheet. Usage is calculated when a billing date is entered.
If this isn't quite right I can work on something else. The trouble with other solutions is transparency & complexity.
David
Then the calc becomes (in words):
=If(NewReading < OldReading, 10^B2 + NewReading  OldReading, NewReading  OldReading)
steve1 
Steve,
That is perfect. exactly what I needed.
If either you or Boon are interested in looking at the entire sheet, just PM me and I will email it to you. I would be interested in your thoughts on streamlining. It is for a private entity so I don't want to post it on line.
Thanks,
Carl"If you can't explain it simply, you don't understand it well enough"
Albert Einstein0 
Nice sheet. Something about keeping the meter digits for a spreadsheet solution doesn't sit well in my head so I was trying to eliminate that without using an IF/THEN and without calculating the meter digits in the formula itself.DIY'er ... ripped out a perfectly good forcedair furnace and replaced it with hot water & radiators.1

Here's a better version. I wanted the user to be able to change the rates or the number of digits on the meter (maybe they get a new and improved meter), without it affecting the earlier (historical readings)
steve1 
The funny part of this is that the part that I was missing was the "^" symbol for scientific notation. I helped my kid with that a few months ago in middle school math. Kind of makes me feel not so smart..."If you can't explain it simply, you don't understand it well enough"
Albert Einstein0 

Steve,
I did and thanks again"If you can't explain it simply, you don't understand it well enough"
Albert Einstein0 
in the case that the new reading is lower than the old reading, you can just add (for example)
power(10,len(B4))
This won't work for a meter that is allowed to go backwards (bidirectional). Then again if the solar array is sized correctly it should take a *really* long time to roll over :)
margTriangle Tube Prestige Solo 110 with Trimax Controls (3x oversized)
950 sqft of WarmBoard on 3 floors, 5 loops acting as one zone0
Categories
 118.1K All Categories
 83K THE MAIN WALL
 2.8K AC, Heat Pumps & Refrigeration
 45 Biomass
 406 Carbon Monoxide Awareness
 17 Chimneys & Flues
 1.6K Domestic Hot Water
 4.4K Gas Heating
 108 Geothermal
 149 IndoorAir Quality
 2.8K Oil Heating
 46 Pipe Deterioration
 706 Plumbing
 4.9K Radiant Heating
 353 Solar
 13.3K Strictly Steam
 2.9K Thermostats and Controls
 50 Water Quality
 31 Industry Classes
 65 Job Opportunities
 13 Recall Announcements