Finding the average

I have a list including some survey scores (about 10.000 rows) as follows:
For example;

1.jpg

I want to achieve the following 4 things:

-I want to find the average of the survey points in 2020
-I want to find the average of the survey points in 2021
-I want to find the average of the scores of 2020 and 2021.
-The survey includes about 10.000 rows. So, how to automatize it? (It is very time consuming to manually find the teacher names in column A and find the averages for each of them)




Maybe we can add them as new columns as follows:
Attachment 763580

Please find the excel file in the attachment.

J2, copied across and down:

=IFERROR(AVERAGEIFS($G:$G,$A:$A,$I2,$C:$C,J$1),"")

a few issues.

1. the value in G10 is stored as TEXT, not a number. Delete & replace with 8.

2. You wanted an average for 2020 and 2021 combined. There are TWO ways of approaching it. Your approach averaged the average. You need to be ABSOLUTELY certain that that is what you REALLY want to do!!! This ofrmula averages the ALL James' scores for BOTH years and produces a different answer.

To take it to an extreme... if the average for 100 results in 2020 was 9 and the average for 2 results in 2021 was 1.... would you REALLY want the overall average to be 5, the average of the average??


This produces the true average:

=SUM(SUMIFS(G:G,A:A,I2,C:C,{2020,2021}))/SUM(COUNTIFS(A:A,I2,C:C,{2020,2021}))

Will there be more than 2 years data? If so, will the average of years be JUST 2 years, or all of them. This is for each individual year:

=IFERROR(SUMPRODUCT(($A$2:$A$10=$H2)*($C$2:$C$10=I$1)*$D$2:$D$10*$E$2:$E$10)/SUMIFS($E:$E,$A:$A,$H2,$C:$C,I$1),"")
copied across 1 column & then down.

This is for ALL years (average of 5 years, if 5 years data are present)

=IFERROR(SUMPRODUCT(($A$2:$A$10=$H2)*$D$2:$D$10*$E$2:$E$10)/SUMIFS($E:$E,$A:$A,$H2),"")

It will need a tweak if it needs to be two out of n years.