Need help auto-updating a formula for week over week change

Hi all,

I was wondering if there's a way to add a formula to calculate week over week % change automatically every week when I enter in new data. Please see the attached excel file for reference.

What I would like to have is the ability for the formulas in c5 and f5 to be able to auto-update to the newest week and the previous week's data instead of manually having to update it each week. So if I were to add a new row with data for week beginning 12/2, the formula in c5 and f5 would automatically update to calculate the week over week variance. I tried researching prior to asking the question on this forum, and I think it may be possible to do it using the index match function, but I'm not sure how to apply it in this case.

Thanks in advance for anyone that can help.

Thanks FDibbins! That worked perfectly. Really appreciate all your help and advice!

I have a similar problem. I'm looking auto update week over week change but the data I'm looking at is daily. I was able to modify your formula by summing (indexing) each of the 7 most recent days and dividing them by the preceding 7 days, but that's an awfully long formula. I'm guessing there may be a shorter way? To display what I mean using the example above, please see the attached. I want to divide the yellow highlighted by the orange highlighted. This is my formula: =(INDEX(C:C,MATCH(MAX($B:$B),$B:$B,0))+INDEX(C:C,MATCH(MAX($B:$B)-1,$B:$B,0))+INDEX(C:C,MATCH(MAX($B:$B)-2,$B:$B,0))+INDEX(C:C,MATCH(MAX($B:$B)-3,$B:$B,0))+INDEX(C:C,MATCH(MAX($B:$B)-4,$B:$B,0))+INDEX(C:C,MATCH(MAX($B:$B)-5,$B:$B,0))+INDEX(C:C,MATCH(MAX($B:$B)-6,$B:$B,0)))/(INDEX(C:C,MATCH(MAX($B:$B)-7,$B:$B,0))+and so on for the preceding 7 days.