Average of several days data at a specific time (with realtime updates)

[mik431]

I'm trying to calculate an average of the last 5 days data at specific times (e.g. five days average at 11am, 12am, 1pm). The data is summed up cumulatively from the beginning of every day.

Please find a simplified example of what I am trying to achieve below:


[TABLE="class: grid, width: 348"]
<tbody>[TR]
[TD]Last 5 days average volume at 13:00
[/TD]
[TD="align: right"] Should be 16
[/TD]
[/TR]
[TR]
[TD]Last 5 days average volume at 12:00
[/TD]
[TD="align: right"]Should be 11
[/TD]
[/TR]
[TR]
[TD]Last 5 days average volume at 11:00
[/TD]
[TD="align: right"]Should be 7
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Volume[/TD]
[/TR]
[TR]
[TD="align: right"]2014-01-18 13:00[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]2014-01-18 12:00[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]2014-01-18 11:00[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]2014-01-17 13:00[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]2014-01-17 12:00[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]2014-01-17 11:00[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]2014-01-16 13:00[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]2014-01-16 12:00[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2014-01-16 11:00[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]2014-01-15 13:00[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]2014-01-15 12:00[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2014-01-15 11:00[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]2014-01-14 13:00[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2014-01-14 12:00[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]2014-01-14 11:00[/TD]
[TD="align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]


To make matters more complicated, I have an Excel add-in that updates the data on an hourly basis and where older data is always shifted down along the list.

I've battled with AVERAGEIFS, but I'm having problems getting the criteria right with the date+time combination

I'm using MS Office 2010 on Windows 7.

Any help or alternative suggestions would be very much appreciated.

 

[AhoyNC]

See if this might work for you.
Columns D,E & F is just where I was checking the formulas in B1 - B3 to make sure they were giving the right average.

The formulas in B1, B2 & B3 are array formulas and must be entered with

Excel Workbook
	A	B	C	D	E	F	G
1	Last 5 days average volume at 13:00	5.00
2	Last 5 days average volume at 12:00	3.80
3	Last 5 days average volume at 11:00	7.20
4				Check Formulas
5		Volume		5	3.8	7.2
6	2014-01-18 13:00	5		5
7	2014-01-18 12:00	7			7
8	2014-01-18 11:00	6				6
9	2014-01-17 13:00	8		8
10	2014-01-17 12:00	4			4
11	2014-01-17 11:00	9				9
12	2014-01-16 13:00	5		5
13	2014-01-16 12:00	3			3
14	2014-01-16 11:00	8				8
15	2014-01-15 13:00	4		4
16	2014-01-15 12:00	1			1
17	2014-01-15 11:00	5				5
18	2014-01-14 13:00	3		3
19	2014-01-14 12:00	4			4
20	2014-01-14 11:00	8				8
21	2014-01-13 13:00	5
22	2014-01-13 12:00	7
23	2014-01-13 11:00	6
24	2014-01-12 13:00	8
25	2014-01-12 12:00	4
26	2014-01-12 11:00	9
27	2014-01-11 13:00	5
28	2014-01-11 12:00	3
29	2014-01-11 11:00	8
30
CTRL-SHIFT-ENTER

 

[mik431]

Thanks, but I need the volume cumulatively for the days at the given time, i.e. at 13:00 it should be the sum of 11, 12 and 13.

Here are what the "should be" formulas look like:

[TABLE="width: 850"]
<tbody>[TR]
[TD]Last 5 days average volume at 13:00[/TD]
[TD]=+SUM(B6:B8;B9:B11;B12:B14;B15:B17;B18:B20)/5[/TD]
[/TR]
[TR]
[TD]Last 5 days average volume at 12:00[/TD]
[TD]=+SUM(B7:B8;B10:B11;B13:B14;B16:B17;B19:B20)/5[/TD]
[/TR]
[TR]
[TD]Last 5 days average volume at 11:00[/TD]
[TD]=+SUM(B8;B11;B14;B17;B20)/5[/TD]
[/TR]
</tbody>[/TABLE]


I'm still looking into the suggested array formulas, though. Maybe they can be tweaked.

 

[markmzz]

Maybe this:

=SUMPRODUCT($B$6:$B$20,-(INT($A$6:$A$20)>INT($A$6-5)),-(ROUND(MOD($A$6:$A$20,1),9)<=ROUND(RIGHT(A1,5),9)))/5

Markmzz

 

[mik431]

Brilliant! This is it Thank you so much Markmzz

 

[mik431]

Well, almost there...

Any ideas on how to configure the above formula to take into account days where no date and data is supplied? At the moment the above works like a charm as long as dates are continuous, but unfortunately the add-in feeding the data skips some days causing the averages to be too low when there are missing days in between (e.g. weekends. I suppose holidays will also pose a similar problem)

 

[markmzz]

Well, almost there...

Any ideas on how to configure the above formula to take into account days where no date and data is supplied? At the moment the above works like a charm as long as dates are continuous, but unfortunately the add-in feeding the data skips some days causing the averages to be too low when there are missing days in between (e.g. weekends. I suppose holidays will also pose a similar problem)

If I understand correctly what you want, maybe this can helps:

=SUMPRODUCT($B$6:$B$20,-(INT($A$6:$A$20)>[COLOR="#0000FF"]WORKDAY($A$6,-5,Holidays)[/COLOR]),-(ROUND(MOD($A$6:$A$20,1),9)<=ROUND(RIGHT(A1,5),9)))/5

Markmzz

 

[mik431]

Thank you very much once again!

WORKDAY would indeed seem to be a solution to the problem. Best case scenario would have been to somehow detect entire dates missing data automatically, but this masterpiece is already better than I could have ever hoped for. I will probably have to go for separate tabs/sheets for different countries, but that is perfectly ok. I'll still monitor this for a while, but for the time being this appears very very good

 

[markmzz]

Thank you very much once again!

WORKDAY would indeed seem to be a solution to the problem. Best case scenario would have been to somehow detect entire dates missing data automatically, but this masterpiece is already better than I could have ever hoped for. I will probably have to go for separate tabs/sheets for different countries, but that is perfectly ok. I'll still monitor this for a while, but for the time being this appears very very good

I'm happy to help.

Markmzz