Help Doing mean of cells on each 6hours time period

[ropbasuel]

Dear all,

I'm facing one difficulty where I ask for your help.

I have a huge amount of hourly data from 2012 to 2018. Each hour I have one data value. What I would like to have is a VBA code that could transform that hourly data to data each 6 to 6 hours. To do that the code should do a mean of all the 6hours and then give that number.

For example I have data of 01-01-2012 from 00:00 to 23:00. The code should write 06:00 doing a mean of 00:00 to 05:00, then write 12:00 doing a mean of 06:00 to 11:00 and so on.

I attach one example of a sample of the data that I have (Columns A and B) and an expected result of the code (Columns D and E)

Thanks to anyone who tries to help

Best Regards

[63falcondude]

Here's a formula approach if you're interested:

D1 =MIN(A:A)+ROWS($1:1)/4
E1 =AVERAGEIFS(B:B,A:A,">="&G1-"6:00",A:A,"<"&G1)

Drag down as far as needed.

[ropbasuel]

Here's a formula approach if you're interested:

D1 =MIN(A:A)+ROWS($1:1)/4
E1 =AVERAGEIFS(B:B,A:A,">="&G1-"6:00",A:A,"<"&G1)

Drag down as far as needed.

Dear 63falcondude,

Thank you so much for your formulas! I really didn't know that there are formulas to do this!

I have tried to use the formulas you told me to, but can you please tell me what should be on cell G1 for the formula to work?

Thank you

[63falcondude]

Whoops, my bad. I put the formulas in other cells and forgot to change the references in the formulas before posting.

D1 =MIN(A:A)+ROWS($1:1)/4
E1 =AVERAGEIFS(B:B,A:A,">="&D1-"6:00",A:A,"<"&D1)

[ropbasuel]

Whoops, my bad. I put the formulas in other cells and forgot to change the references in the formulas before posting.

D1 =MIN(A:A)+ROWS($1:1)/4
E1 =AVERAGEIFS(B:B,A:A,">="&D1-"6:00",A:A,"<"&D1)

Dear 63falcondude,

There is no problem! Thank you for your reply.

I'm still trying to get the "E1" cell formula to work but maybe I'm doing something wrong because I'm getting the "#NAME?" error.

Can you please have a look on my file again?

Thank you

[63falcondude]

That's strange... When I open the workbook from post #5, I see the #NAME? error but then when I click in the cell and then hit Enter, I get 2.21.

The #NAME? error suggests that the formula contains unrecognized text. Excel 2007 supports AVERAGEIFS so I'm not sure what's going on.

Maybe it has to do with the formatting of the time in the formula. You can try this instead in E1:
=AVERAGEIFS(B:B,A:A,">="&D1-TIME(6,0,0),A:A,"<"&D1)

[ropbasuel]

That's strange... When I open the workbook from post #5, I see the #NAME? error but then when I click in the cell and then hit Enter, I get 2.21.

The #NAME? error suggests that the formula contains unrecognized text. Excel 2007 supports AVERAGEIFS so I'm not sure what's going on.

Maybe it has to do with the formatting of the time in the formula. You can try this instead in E1:
=AVERAGEIFS(B:B,A:A,">="&D1-TIME(6,0,0),A:A,"<"&D1)

Dear 63falcondude,

I've tried the new formula that you gave me, and I got the same "#NAME?" error. I think I know why... My Excel isn't in English language, so probably there is something that is messing with the calculation of the formula...

I will try to see what is going wrong with this.

Again, thank you so much!

[63falcondude]

My Excel isn't in English language

That'll do it. I'll put the formulas into the workbook so that they will be automatically translated for you when you open it.

See attachment.

[ropbasuel]

Dear 63falcondude,

That solved my problem! Opening your workbook automatically translated the formulas to my language, something that I was doing manually but I was using the wrong formula translation

I want to truly thank your help and patience with me. This will help me a lot! I will give you the reputation you deserve.

Thank you so much!

Best Regards

[63falcondude]

Great! Happy to help.

Thanks for the rep!