Average data by date excluding the first value

  • 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.


    DateDataDifferenceAverage
    5/23/20205---2
    5/23/20203
    -2-2
    5/23/20201-2-2
    5/24/202010
    9-8
    5/24/20202
    -8-8
    5/25/20201816-4
    5/25/20203-15-4
    5/25/202041-4
    5/24/202062-4
  • Bosco, as I stated in my original post, I cannot use the formula you are proposing for column C. I must calculate the difference between the current and previous value for all data points not just the ones which occur on the same day. The question was how to arrange a formula which will allow me to calculate the daily average of those differences, excluding the first value for each day.

  • Bosco, as I stated in my original post, I cannot use the formula you are proposing for column C. I must calculate the difference between the current and previous value for all data points not just the ones which occur on the same day. The question was how to arrange a formula which will allow me to calculate the daily average of those differences, excluding the first value for each day.

    Then,


    D2, formula copied down :


    =SUMPRODUCT((A$2:A$10=A2)*(A$1:A$9=A$2:A$10)*N(+C$2:C$10)/(COUNTIF(A$2:A$10,A2)-1))


  • Wow Bosco thank you so much!! That is exactly what I was looking for. My last question though is the formula you gave me assumes that all I have is 10 data points and that the data never changes size. The data I'm working with is in a table that can change in size if more information is added. I modified your formula for my purposes to reference the Table[Date] and Table[Difference] instead where you mention A2:A10 and C2:C10 in your example. My question for you is how do I modify the formula to handle the A1:A9 when the table changes in size?