Adjusting dynamic range for each record and nesting within sumproduct function

[bushbaker]

Hi there,

I have figured out (thanks to some highly informative posts in this forum) how to create a weighted average for the most up to date record of a student based on their test scores and when they did the test.

However the issue I am trying to solve is how to embed a dynamic range so that I can go back in time through a students weighted averages and see whether they are improving or not. The condition on the weighting factor is that the test was carried out in the last 200 days. Hence for each past test date I want to recalculate the cumulative days (baseline weighting factor-Column D) up to 200 days prior and embed this into the formula highlighted in the file attached so I can replicate formula for each test date.

I appreciate any insights you are able to offer to help me solve this problem.

Regards