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

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

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

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

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

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

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

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

12. 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. 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. Re: Missing values how to Average each midnight (after 23 values)

Originally Posted by Keebellah
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. Re: Missing values how to Average each midnight (after 23 values)

Originally Posted by samba_ravi
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

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

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