7-Day Rolling Average, Based on Date in P1

[surkdidat]

Hi

In Column P I have a date (ie 1 March 2021)

In column B (starting in B4:B100) - counting downwards, so the most recent date is in B4 - 23 March 2021 to B100 21 December 2020)

In Column C I have figures corresponding to those dates

I want it to look up the date in P1, and then reference that to column B's dates, and then use the figure in Column C and calculate the average

P1	01 March 2021	AVERAGE :25
		(Average of 10, 18, 36, 42, 37, 19, 13)

B4	23 March 2021	42
B5	22 March 2021	36
....	....	......
B27	01 March 2021	10
B28	28 February 2021	18
B29	27 February 2021	36
B30	26 February 2021	42
B31	25 February 2021	37
B32	24 February 2021	19
B33	23 February 2021	13
B34	22 February 2021	17

 

[jasonb75]

This is how I would do it in office 365.

=AVERAGE(INDEX(C4:C100,SEQUENCE(7,,MATCH(P1,B4:B100,-1))))

If you have an older version of excel then you will need a different formula. Please click on your username at the top right of the page then update your account details to show the correct version of excel that you are using.

 

[surkdidat]

This is how I would do it in office 365.

=AVERAGE(INDEX(C4:C100,SEQUENCE(7,,MATCH(P1,B4:B100,-1))))

If you have an older version of excel then you will need a different formula. Please click on your username at the top right of the page then update your account details to show the correct version of excel that you are using.

Hi - apologies updated - am on Excel 2016

 

[jasonb75]

In that case, try

=AVERAGE(OFFSET($C$4,MATCH(P1,$B$4:$B$100,-1)-1,0,7,1))

 

[surkdidat]

In that case, try

=AVERAGE(OFFSET($C$4,MATCH(P1,$B$4:$B$100,-1)-1,0,7,1))

Thanks very much, very helpful.... Tried to get my head around OFFSET without success, so will delve into the formula later so I can understand what it is doing, and not just putting the solution in my sheet without understanding how it works!