Aggregate forumla help

[seanyc]

I am trying to show a 3-day average of historical data, that reflects the average at the current time. So, for example, I would want to display the 3 day average of the data at 8:30 am, and then at 8:31 am for it to update to reflect the 3 day average at that time. Does anyone have any ideas how represent this using a formula?

[NBVC]

I am not sure what your data look like, but if it is consistent, then can you use =AVERAGE(B1,B11,B21) where B1, B11 and B21 would be the last 3 days' 8:30 entries? Or is it more complicated? And if so, please post sample dataset and expected results.

[seanyc]

My data set is massive. I have three sets of data. Day 1, Day 2, and Day 3, which are 1, 2, and 3 days ago, respectively. Every fifteen seconds of the day, there is a new data point. I need to build a formula that displays the average of these data points across the three days at each specific 15 second interval, or each minute interval is that is easier. So, for example, every minute the data should change to reflect the new running average for that same minute over the past three days.

[NBVC]

Please upload a sample workbook... with less data and show what you mean.

[seanyc]

I've attached an example. I am looking for a way to take the average of each three "running average" columns at each 1 minute interval (I understand that not every column has a value for every interval). So I am looking for a formula that reflects the average at each minute interval and displays the current time minute interval's aggregate average. Thank you very much for helping

[NBVC]

Assuming you have a current time in O2, try:

=SUMPRODUCT(--(MOD($A$2:$K$500,1)=MOD(O2,1)),$C$2:$M$500)/SUMPRODUCT(--(MOD($A$2:$K$500,1)=MOD(O2,1)))

you can copy formula down.

Is this what you need?

[seanyc]

Assuming you have a current time in O2, try:

=SUMPRODUCT(--(MOD($A$2:$K$500,1)=MOD(O2,1)),$C$2:$M$500)/SUMPRODUCT(--(MOD($A$2:$K$500,1)=MOD(O2,1)))

you can copy formula down.

Is this what you need?

Thank you for that. I tried that and placed =NOW() in O2 to display current time and date and I am getting an error when I paste the formula. Anything else I should be doing?

[NBVC]

Are you getting the Div/0 error? Do you have actual cells that do match? If not, and you want blank returned.. then:

Please Login or Register  to view this content.

[seanyc]

Are you getting the Div/0 error? Do you have actual cells that do match? If not, and you want blank returned.. then:

Please Login or Register  to view this content.

I'm not quite sure. I'm fairly new to excel and I am not positive how to incorporate the formula into my spreadsheet. Any chance you could help me with that? I'd really appreciate it. Sorry to be a pain

[NBVC]

See attached.

=NOW() is in O2.. and formula in P2... giving blank since no matches found.

[seanyc]

Ok thank you very much. How do I now display the average 3 day volume at a running time?

[NBVC]

Use the same formula, but change the $C$2:$M$2 reference to $B$2:$L$2

[seanyc]

Ok I changed it to that and it still just displays a blank yellow cell. By the way thank you very much for helping me I really appreciate it.

[NBVC]

is the time in O2 also somewhere in the data you are looking in?