Insert new dates with dynamic formula to include new range

[NotTheBestAtExcel]

I need to insert new dates as I add new dates I need to calculate the 4 wk, 8 wk, & 13 wk. For example, if I inserted 2/10/2024, I would need the formula to take into account the new date and provide the 4 wk, 8 wk, & 13 wk average.


I used this formula:
=ROUND(AVERAGE(OFFSET(D4, 0, COUNTA(D3:P3)-4, 1, 4))*4.33, 0)

But, when I inserted a new date into the column, the formula would not take into account the new date. Every month I'll be adding new dates and I need to formula to work dynamically.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=ROUND(AVERAGE(TAKE(DROP(D5:R5,,-1),,-4))*4.33,0)

Change the -4 to -8 for the last 5 weeks

 

[NotTheBestAtExcel]

=ROUND(AVERAGE(TAKE(DROP(D5:R5,,-1),,-4))*4.33,0)

Hello,

Thanks for helping. I tried the formula and I get the average of 0, looks like that didn't work. the 4 week average for 2/3 through 2/24 should be 74 for the ITEM "Test123"

 

[Fluff]

Oops forgot to change the range after testing, in Q5 it should

=ROUND(AVERAGE(TAKE(DROP(D5:Q5,,-1),,-4))*4.33,0)

 

[NotTheBestAtExcel]

Figured it out, added INDEX
=ROUND(AVERAGE(INDEX(5:5,,COLUMN(Q5)-4):INDEX(5:5,,COLUMN(Q5)-1))*4.33,0)

 

[Peter_SSs]

Figured it out, added INDEX

Did you try that shorter adjusted formula in post 4?