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.

Quote from bosco_yip

Try...………………...

1] In C2, formula copied down :

=IF(A2=A1,B2-B1,0)

2] In D2, formula copied down :

=AVERAGEIFS($C$2:$C$10,$C$2:$C$10,"<>0",$A$2:$A$10,A2)

Display More

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.

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?