+ Reply to Thread
Results 1 to 18 of 18

Missing values how to Average each midnight (after 23 values)

  1. #1
    Registered User
    Join Date
    11-11-2018
    Location
    Australia
    MS-Off Ver
    MS Ofiice 2010
    Posts
    6

    Missing values how to Average each midnight (after 23 values)

    Hello community

    I am stuck at a very basic problem. I have an hourly data (0.00 hour to 23:00 hour for whole year) for 3 years and every midnight 0.00 hour the data is missing. I need to find average of next 23 hours to substitute at 0.00 hours (same calendar day) so I have complete data and I can use my mathematical models.

    If I go by conventional way of finding average it will take a huge time. Is there a formula that can help for the whole worksheet?

    Thank you very much in advance
    Eve

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Missing values how to Average each midnight (after 23 values)

    Hi, welcome to the forum.
    If I understand you correctly you need a formula to complete the 00:00-01:00 that is missing, is this correct?
    How is the data updated now?
    Macro?
    In that case you can edit your macro to do an extra step after 23:00 update has been completed to get the average of 01:00-23:00 and place it in the 00:00
    Keep in mind that 00:00 belongs to the next day, entire day is 00:00:00 through 23:59:59
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Missing values how to Average each midnight (after 23 values)

    Yes there is a formula, averageif, but without seeing a sample of your data it is hard to be more specific.
    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  4. #4
    Registered User
    Join Date
    11-11-2018
    Location
    Australia
    MS-Off Ver
    MS Ofiice 2010
    Posts
    6

    Re: Missing values how to Average each midnight (after 23 values)

    Thank you so much. Attaching a sample just enough to demonstrate the need. At the moment, I am doing everthing manually.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-11-2018
    Location
    Australia
    MS-Off Ver
    MS Ofiice 2010
    Posts
    6

    Re: Missing values how to Average each midnight (after 23 values)

    Thank you. I am doing it in Excel at the moment.

  6. #6
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Missing values how to Average each midnight (after 23 values)

    If you say
    I am doing it in Excel at the moment
    where were you doing it in i the first place?

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Missing values how to Average each midnight (after 23 values)

    I took a quick look and my solution would be a macro for the columns where the missing value is NOT 00:00 but somewhere in between
    I changed the formula in your sample to AVERAGEIF() and that works for the sets where 00:00 is missing, just one formula you can copy to each row with 00:00

    Like I said the alternative is a MACRO and thus a macro-embedded file
    Attached Files Attached Files

  8. #8
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Missing values how to Average each midnight (after 23 values)

    M2=AVERAGE(OFFSET(M2,HOUR($B2)*-1,,24,1))
    Try the above formula, copy and paste it where you want to get average
    generally we get circular reference in the above case because your excel options were changed to enable iterative calculations.
    Last edited by samba_ravi; 11-13-2018 at 03:44 AM.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Missing values how to Average each midnight (after 23 values)

    Out of curiosity, Why the average of the entire series 00:00-23:00? If you take the average of the value above 23:00 and the value below 01:00 it's about right too.
    The process seems to be continuous so if one is skipped the average or mean could also be the previous and the following.
    Just an idea and will make it easier to solve.
    You could do it in an additional worksheet, I'll try and put it together for you to show you what I mean.
    This data worksheet, how is it populated? Filled?

  10. #10
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Missing values how to Average each midnight (after 23 values)

    @samba_ravi: will this work in the other columns where the missing value is not at 00:00?

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Missing values how to Average each midnight (after 23 values)

    This is what I was thinking of.
    The worksheet KEEBELLAH is only formulas and reads the data from BEFORE
    Columns G-M have extended formulas to include the average if a value is missing (any row)
    Worksheet is protected but no password to avoid a user deleting the formulas
    So just update BEFORE and the data is refreshed if you have set calculation to automatic else press F9
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Missing values how to Average each midnight (after 23 values)

    it very much depends on your need
    you can as Keebellah suggests get a missing various ways
    =AVERAGE(G3:G25) will work its just how you need to paste it into the data

    if you goto g74 (the row below the bottom of your data) and type average(g75:g97) , select g74:g97 and copy to g2:J73 with paste special skip blanks it will do what you are currently doing

  13. #13
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Missing values how to Average each midnight (after 23 values)

    Have you checked my file? I covered (I think) the entire set of columns G-M all the way to 1000 rows.
    Let's hear what the OP thinks and get more info as to the way the dat is being acquired en entered in what the OP named BEFORE worksheet

  14. #14
    Registered User
    Join Date
    11-11-2018
    Location
    Australia
    MS-Off Ver
    MS Ofiice 2010
    Posts
    6

    Re: Missing values how to Average each midnight (after 23 values)

    Quote Originally Posted by Keebellah View Post
    Have you checked my file? I covered (I think) the entire set of columns G-M all the way to 1000 rows.
    Let's hear what the OP thinks and get more info as to the way the dat is being acquired en entered in what the OP named BEFORE worksheet
    Thank you very much.

  15. #15
    Registered User
    Join Date
    11-11-2018
    Location
    Australia
    MS-Off Ver
    MS Ofiice 2010
    Posts
    6

    Re: Missing values how to Average each midnight (after 23 values)

    Quote Originally Posted by samba_ravi View Post
    M2=AVERAGE(OFFSET(M2,HOUR($B2)*-1,,24,1))
    Try the above formula, copy and paste it where you want to get average
    generally we get circular reference in the above case because your excel options were changed to enable iterative calculations.
    can you please explain this formula. Why there is -1 and 1

  16. #16
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Missing values how to Average each midnight (after 23 values)

    Who're your asking? There is no *-1 in my sample file

  17. #17
    Registered User
    Join Date
    11-11-2018
    Location
    Australia
    MS-Off Ver
    MS Ofiice 2010
    Posts
    6

    Re: Missing values how to Average each midnight (after 23 values)

    I was asking samba_ravi this question. If you know you can reply too why -1 and 1 in formula below

    ''M2=AVERAGE(OFFSET(M2,HOUR($B2)*-1,,24,1))
    Try the above formula, copy and paste it where you want to get average
    generally we get circular reference in the above case because your excel options were changed to enable iterative calculations. ''

  18. #18
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Missing values how to Average each midnight (after 23 values)

    I haven't seen where samba_ravi used it but all it does is set the offset to the column that the contents in B@ refer to to the previous column
    If Hour(b2) = let's say 7 then the results is 6

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 12-19-2014, 06:38 AM
  2. Replies: 1
    Last Post: 04-30-2014, 05:49 PM
  3. Replies: 5
    Last Post: 01-24-2014, 04:53 AM
  4. How to calculate Average of daily data to fill missing values
    By mahjid in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-11-2013, 05:46 AM
  5. Find missing values - based on looking up a list, comparing against index values
    By anakaine in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 01-22-2013, 01:31 AM
  6. Calculate weighted average with missing values
    By Deiseman in forum Excel General
    Replies: 9
    Last Post: 06-08-2010, 10:03 AM
  7. Replies: 1
    Last Post: 04-02-2005, 01:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1