Welcome! Here are the website rules, as well as some tips for using this forum.

# Excel Guru?

Member Posts: 7,466
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.

Carl

"If you can't explain it simply, you don't understand it well enough"
Albert Einstein

• Member Posts: 6,237
edited June 2017
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:
steve
• Member Posts: 7,466
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.
"If you can't explain it simply, you don't understand it well enough"
Albert Einstein
• Member Posts: 6,237
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.
steve
• Member Posts: 259
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
DIY'er ... ripped out a perfectly good forced-air furnace and replaced it with hot water & radiators.
• Member Posts: 6,237
edited June 2017
Here ya go. PM me if you need instructions
Truth be told, it would be much better on a database-more functions, more options.
But let me know if it needs any tweaks
steve
• Member Posts: 6,237
screenshot
steve
• Member Posts: 6,237
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

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.
Then the calc becomes (in words):
steve
• Member Posts: 7,466
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 Einstein
• Member Posts: 259
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 forced-air furnace and replaced it with hot water & radiators.
• Member Posts: 6,237
edited June 2017
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)
steve
• Member Posts: 7,466
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 Einstein
• Member Posts: 6,237
It wasn't initially intuitive to me. Did you get my return email with your slightly modified file?
steve
• Member Posts: 7,466
Steve,
I did and thanks again
"If you can't explain it simply, you don't understand it well enough"
Albert Einstein
• Member Posts: 54
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 (bi-directional). Then again if the solar array is sized correctly it should take a *really* long time to roll over :-)

marg
Triangle Tube Prestige Solo 110 with Trimax Controls (3x oversized)
950 sqft of WarmBoard on 3 floors, 5 loops acting as one zone