Reference offset table range in formula

I am working with the following formula that calculates an average value, excluding the first difference: =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)) which works really well when I have a static range that I'm not adding to. What I want to do now is create a dynamic formula that works with a table so that the formula will work regardless of how much data I decide to work with. The problem that I am having is that I am having is I can change A$2:A$10 to Table1[Date] and C$2:C$10 to Table1[Difference] but I can't figure out how to address the range A$1:A$9. Is there a way that I can set up a named range that automatically updates? or use the just incorporate the OFFSET formula into the formula that I'm modifying?

With a little trial and error, I think I found a formula for a named reference that will work to replace the A$1:A$9 in the formula to make it dynamic: =OFFSET($A$1,0,0,COUNTA($A:$A)-1,1)