Change Cell Value Average As Weeks Progress

[alexfooty]

Hi

From the images below this is what I am trying to do - with either VBA or formula.

The value for Week 1 in cell AJ42 to equal the Week 1 average in cell AV40
Similarly, Weeks 2 & 3 in cells AJ44 & AJ46 to equal the averages in cells AV41 & AV42 respectively.

However, as in the second image, when the next Week 1 in cell AN43 becomes populated, I would like cell AJ42 to equal
the average in cell AV43. The same would apply to Weeks 2 & 3 as the weeks progress.

The table covers rows 40 through to row 73.

Hope someone can understand this - any help would be greatly appreciated.

 

[Fluff]

How about

+Fluff New.xlsm
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2
3					Week 1	03/08/2020	10	11	12	13	14	15	16		13
4		Week 1	40		Week 2	10/08/2020	20	21	22	23	24	25	26		23
5					Week 3	17/08/2020	30	31	32	33	34	35	36		33
6		Week 2	23		Week 1	24/08/2020	40								40
7					Week 2	31/08/2020
8		Week 3	33		Week 3	07/09/2020
9					Week 1	14/09/2020
10					Week 2	21/09/2020
11					Week 3	28/09/2020
Master

Cell Formulas
Range	Formula
J4	J4	=LOOKUP(2,1/(N3:N11<>"")/(L3:L11=I4),V3:V11)
J6	J6	=LOOKUP(2,1/(N3:N11<>"")/(L3:L11=I6),V3:V11)
J8	J8	=LOOKUP(2,1/(N3:N11<>"")/(L3:L11=I8),V3:V11)
V3:V11	V3	=IF(N3="","",AVERAGE(N3:T3))

 

[alexfooty]

Fluff - What a star performer you are. Works admirably - just what I needed again. Many thanks

 

[Fluff]

You're welcome & thanks for the feedback.