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

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