Hi,
I am working with data that has a a series of dates and then data for each date. I then take each number from the data column and take the difference between each value and the value in the row above it. What I am looking to do is now take the average of those differences based on the corresponding date but I need to exclude the first difference for each date from the average since that difference is based on the difference between data on two days. I know I could just use a formula which would skip calculating a difference if the dates don't match and then calculate an average using the AVERAGEIF function but I need to keep the rows with differences between dates for another formula later on. Below is a table of dummy data showing what I would expect for the averages column. Please note that as I have shown below, the formula needs to be dynamic meaning that each day may a different number of data rows to average compared to the previous day or next day.
Date | Data | Difference | Average |
5/23/2020 | 5 | -- | -2 |
5/23/2020 | 3 | -2 | -2 |
5/23/2020 | 1 | -2 | -2 |
5/24/2020 | 10 | 9 | -8 |
5/24/2020 | 2 | -8 | -8 |
5/25/2020 | 18 | 16 | -4 |
5/25/2020 | 3 | -15 | -4 |
5/25/2020 | 4 | 1 | -4 |
5/24/2020 | 6 | 2 | -4 |