lundi 14 décembre 2020

Moving average if the difference between two date-hours is less than value

I have a dataset in Excel consisting of 800,000 rows with data, there is registered a value every 4 minutes. I would like to do a moving/running average where I look across 3 hours (so 15 rows of data). However due to outages and other unforeseen events, the data will quite often have a lot times where the is a much larger time between registrations, perhaps hours or days.

I'm trying do the 3 hours moving average but only if the difference between the last items in the running average has a time difference less than 3 hours. If the time difference is above 3 hours, it needs to start a new running average from that value. Is this possible using Excelformulas?

I've added a sample data below, covering column A and B.

Date/time Data
06-09-2017 18:25 1,473
06-09-2017 18:29 1,488
06-09-2017 18:32 1,537
06-09-2017 18:36 1,66
06-09-2017 18:41 1,863
06-09-2017 18:45 2,124
06-09-2017 18:48 2,364
06-09-2017 18:53 2,57
06-09-2017 18:57 2,723
06-09-2017 19:00 2,853
06-09-2017 19:04 2,959
06-09-2017 19:09 3,082
06-09-2017 19:13 3,246
06-09-2017 19:16 3,47
06-09-2017 19:20 3,699
06-09-2017 19:25 3,883
06-09-2017 19:29 4,001
06-09-2017 19:32 4,025
06-09-2017 19:36 3,942
06-09-2017 19:41 3,772
06-09-2017 19:45 3,564
06-09-2017 19:48 3,323
06-09-2017 19:53 3,075
06-09-2017 19:57 2,842
06-09-2017 20:00 2,64
06-09-2017 20:04 2,475
06-09-2017 20:09 2,347
06-09-2017 20:13 2,257
06-09-2017 20:16 2,209
07-09-2017 20:20 2,21
07-09-2017 20:25 2,23
07-09-2017 20:29 2,225
07-09-2017 20:32 2,169
07-09-2017 20:36 2,069
07-09-2017 20:41 1,938
07-09-2017 20:45 1,797
07-09-2017 20:48 1,657
07-09-2017 20:53 1,521
07-09-2017 20:57 1,401
07-09-2017 21:00 1,289
07-09-2017 21:04 1,187
07-09-2017 21:09 1,097
07-09-2017 21:13 1,017
07-09-2017 21:16 0,948
07-09-2017 21:20 0,889
07-09-2017 21:25 0,839
07-09-2017 21:29 0,795
07-09-2017 21:32 0,757
07-09-2017 21:36 0,721
07-09-2017 21:41 0,688
07-09-2017 21:45 0,656
07-09-2017 21:48 0,625
07-09-2017 21:53 0,595
07-09-2017 21:57 0,566
07-09-2017 22:00 0,539
07-09-2017 22:04 0,511
07-09-2017 22:09 0,487
07-09-2017 22:13 0,475
07-09-2017 22:16 0,468
07-09-2017 22:20 0,461

Aucun commentaire:

Enregistrer un commentaire