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

#### mik431

##### New Member
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:

 Last 5 days average volume at 13:00 Should be 16 Last 5 days average volume at 12:00 Should be 11 Last 5 days average volume at 11:00 Should be 7 Volume 2014-01-18 13:00 5 2014-01-18 12:00 7 2014-01-18 11:00 6 2014-01-17 13:00 8 2014-01-17 12:00 4 2014-01-17 11:00 9 2014-01-16 13:00 5 2014-01-16 12:00 3 2014-01-16 11:00 8 2014-01-15 13:00 4 2014-01-15 12:00 1 2014-01-15 11:00 5 2014-01-14 13:00 3 2014-01-14 12:00 4 2014-01-14 11:00 8

<tbody>
</tbody>

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.

Last edited:

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### AhoyNC

##### Well-known Member
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
ABCDEFG
1Last 5 days average volume at 13:005.00
2Last 5 days average volume at 12:003.80
3Last 5 days average volume at 11:007.20
4Check Formulas
5Volume53.87.2
62014-01-18 13:0055
72014-01-18 12:0077
82014-01-18 11:0066
92014-01-17 13:0088
102014-01-17 12:0044
112014-01-17 11:0099
122014-01-16 13:0055
132014-01-16 12:0033
142014-01-16 11:0088
152014-01-15 13:0044
162014-01-15 12:0011
172014-01-15 11:0055
182014-01-14 13:0033
192014-01-14 12:0044
202014-01-14 11:0088
212014-01-13 13:005
222014-01-13 12:007
232014-01-13 11:006
242014-01-12 13:008
252014-01-12 12:004
262014-01-12 11:009
272014-01-11 13:005
282014-01-11 12:003
292014-01-11 11:008
30
CTRL-SHIFT-ENTER

#### mik431

##### New Member
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:

 Last 5 days average volume at 13:00 =+SUM(B6:B8;B9:B11;B12:B14;B15:B17;B18:B20)/5 Last 5 days average volume at 12:00 =+SUM(B7:B8;B10:B11;B13:B14;B16:B17;B19:B20)/5 Last 5 days average volume at 11:00 =+SUM(B8;B11;B14;B17;B20)/5

<tbody>
</tbody>

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

#### markmzz

##### MrExcel MVP
Maybe this:

Code:
``=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

Last edited:

#### mik431

##### New Member
Brilliant! This is it Thank you so much Markmzz

#### mik431

##### New Member
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

##### MrExcel MVP
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:

Code:
``=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

##### New Member
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

##### MrExcel MVP
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

Replies
2
Views
439
Replies
11
Views
228
Replies
2
Views
119
Replies
5
Views
123
Replies
4
Views
88

1,195,623
Messages
6,010,748
Members
441,567
Latest member
Flitbee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back