Dynamic Average if formula

Do you simply want the average of the values in column I for each person? Enter the formula in row 2 and copy down.

Values as displayed

	A	B	C
1		M2	Answer
2	Tim		3.5
3	Sue		4.3
4	Fred		4.0

Underlying formulas

	A	B	C
1		M2	Answer
2	Tim		=AVERAGEIF($F:$F,$A2,$I:$I)
3	Sue		=AVERAGEIF($F:$F,$A3,$I:$I)
4	Fred		=AVERAGEIF($F:$F,$A4,$I:$I)

OK, I didn't understand that from your post. Updated formula.

Values as displayed

	A	B	C
1		M2	Answer
2	Tim		3.5
3	Sue		4.3
4	Fred		4.0

Underlying formulas

	A	B	C
1		M2	Answer
2	Tim		=AVERAGEIF($F:$F,$A2,OFFSET($A:$A,0,MATCH("M2 Assessment",$1:$1,0)-1))
3	Sue		=AVERAGEIF($F:$F,$A3,OFFSET($A:$A,0,MATCH("M2 Assessment",$1:$1,0)-1))
4	Fred		=AVERAGEIF($F:$F,$A4,OFFSET($A:$A,0,MATCH("M2 Assessment",$1:$1,0)-1))

In B2 copied down:

=LET(f,FILTER($G$2:$J$15,$F$2:$F$15=A2),AVERAGE(FILTER(f,LEFT($G$1:$J$1,2)=$B$1)))

Should do, but if you want solutions for GoogleSheets, you should be posting in the Other Platforms section.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Thanks for the rep, but you forgot to mark the thread as SOLVED. Please do so.