Excel Guru?

Zman

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

STEVEusaPA

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

Zman

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.

STEVEusaPA

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.

Boon

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

STEVEusaPA

Here ya go. PM me if you need instructions
Hopefully it will load.
Truth be told, it would be much better on a database-more functions, more options.
But let me know if it needs any tweaks

STEVEusaPA

screenshot

STEVEusaPA

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):
=If(NewReading < OldReading, 10^B2 + NewReading - OldReading, NewReading - OldReading)

Zman

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

Boon

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.

STEVEusaPA

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)

Zman

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

STEVEusaPA

It wasn't initially intuitive to me. Did you get my return email with your slightly modified file?

Zman

Steve,
I did and thanks again

margsuarez

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