1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved Dates and formula issue

Discussion in 'Business Applications' started by bleep69, Sep 19, 2019.

Advertisement
  1. bleep69

    bleep69 Thread Starter

    Joined:
    Nov 21, 2008
    Messages:
    135
    Hi there,

    I am monitoring my electrical consumption via a spreadsheet, but the hectic power cuts here means that on some days I am unable to take a reading as the meter is off the whole time and doesn't display units. This is resulting in large spikes in my data, as when I get a reading it covers several days usage.

    I am looking to insert a formula in every blank cell in the U column that will calculate how many days have passed since the previous reading, look at the difference in consumption between the start and the opening balance, and put the average power consumption for the missing days in the cell, and don't have a clue where to start. I could do it manually without much trouble, but thought this would be an interesting thing to learn if someone could take the time to show me how to do it.

    Any help appreciated.
     

    Attached Files:

  2. byoung7998

    byoung7998

    Joined:
    Dec 21, 2014
    Messages:
    15
    I don't quite know what you mean by the "difference in consumption between the start and the opening balance" because aren't those the same numbers? Start and opening?

    Anyway, an easy way to do at least a good part of what you want would be to use an =IFERROR(current function, what to do if current function returns an error).

    Since your current function does a vlookup of the dates the readings were taken, if a particular date, like May 25, is searched for, that vlookup will return an "#N/A" error since it is not in your column A of the table.

    The IFERROR function gives the result of the normal function you want, but if that function returns an error, then it will do the calculation after the comma inside the parenthesis.

    So what you will want is (for Cell U2):

    =IFERROR(VLOOKUP(T2,Table1[[Date]:[Remaining units]],2,FALSE),what to do if the date is not found in the tabular data)

    Good luck!
     
  3. bleep69

    bleep69 Thread Starter

    Joined:
    Nov 21, 2008
    Messages:
    135
    Hey, I had forgotten about this, thanks for responding.

    The table in columns AB and C shows the dates on which I took readings, and what those readings were. The table in columns T and U is the same information, but with every day listed. I used VLOOKUP to insert the readings on the dates that readings were taken, but this leaves large blank periods during which I never managed to get readings. I want to fill in all the blank days in column U with the average power used on those days, which would be calculated by looking at the last balance, deducting the next reading, and dividing by the number of days between the 2. The problem is that the days between readings vary, and I do not know how to calculate that change via a formula.
     
  4. byoung7998

    byoung7998

    Joined:
    Dec 21, 2014
    Messages:
    15
    You can do "math" with dates like any other number. Excel treats dates as numerical values (and partial dates as decimal parts of numbers). 1/1/1900 is considered date 1. 9/26/2019 is considered date 43,734. Each future day is 1 number higher than the previous day. So 9/27/2019 will be 43,735. The time between each day is then split up proportionately from 0.00000 (up to 15 decimals) to 0.99999 (also up to 15 decimals) 12:00:00 AM (midnight) is considered 0.0000, and it increments up until 0.99999 at 11:59:59 PM (fractional seconds are also used in the calculations). I've attached your spreadsheet with the results in column C.

    This means you will no longer need columns T and U.
     

    Attached Files:

  5. bleep69

    bleep69 Thread Starter

    Joined:
    Nov 21, 2008
    Messages:
    135
    Man, that is such a simple fix, should have been able to work that out myself!

    Many thanks for the help, have a good day!
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...

Short URL to this thread: https://techguy.org/1233164

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice