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?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!