trouble with OFFSET function for updating a graph

I am not quite sure where to enter what you have told me. I have attached a sample with the same problem I am facing. One other thing I would like to point out: The person working on this before me set the average column all the way through 30000 rows... I was thinking I could possible use an if function to only fill in the average where a date exists. I am going to start working on that right now, but here is a copy of what Ive got.

I have attached an updated example that explains the problem/desired outcome more clearly.