Click here to Find a Contractor in your area.
Welcome! Here are the website rules, as well as some tips for using this forum.
Need to contact us? Visit https://heatinghelp.com/contact-us/.

Excel Guru?

Zman
Zman Member Posts: 6,986
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

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

Comments

  • STEVEusaPA
    STEVEusaPA Member Posts: 5,313
    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:
    -If NewReading < OldReading //a rollover
    NewReading + 1000000 - OldReading
    -otherwise NewReading -OldReading
    steve
  • Zman
    Zman Member Posts: 6,986
    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 Einstein
  • STEVEusaPA
    STEVEusaPA Member Posts: 5,313
    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
  • Boon
    Boon Member Posts: 252
    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.
  • STEVEusaPA
    STEVEusaPA Member Posts: 5,313
    edited June 2017
    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
    steve
    Zman
  • STEVEusaPA
    STEVEusaPA Member Posts: 5,313
    screenshot
    steve
  • STEVEusaPA
    STEVEusaPA Member Posts: 5,313
    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)
    steve
    Zman
  • Zman
    Zman Member Posts: 6,986
    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
  • Boon
    Boon Member Posts: 252
    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.
    Zman
  • STEVEusaPA
    STEVEusaPA Member Posts: 5,313
    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
    Zman
  • Zman
    Zman Member Posts: 6,986
    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
  • STEVEusaPA
    STEVEusaPA Member Posts: 5,313
    It wasn't initially intuitive to me. Did you get my return email with your slightly modified file?
    steve
    Zman
  • Zman
    Zman Member Posts: 6,986
    Steve,
    I did and thanks again
    "If you can't explain it simply, you don't understand it well enough"
    Albert Einstein
  • margsuarez
    margsuarez 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