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