Hello,
I have a spreadsheet with different people's scores for a test over time:
A1:A1000 contains names
B1:B1000 contains test 1 scores
C1:C1000 contains test 1 dates
D1:D1000 contains test 2 scores
E1:E1000 contains test 2 dates etc. with space for up to 100 tests per person to be recorded.
I am looking to create a formula that will calculate the annual average scores for each person for every year from 1995 to 2020, and these will be at the end of each row under the headings "1995 annual average" to "2020 annual average". This means the scores to be included for the calculation of each yearly average are dependent on their date. For example the 2017 calculation should only be an average of the scores with a corresponding date in the year 2017.
I have tried labelling the data rage B:B,D:D,F:F,H:H...GS:GS "scores" and the data range C:C,E:E...GT:GT "dates" to put into the formula. I have tried a few variations including:
AVERAGEIF(dates,AND(>=01/01/2017,<=31/12/2017),scores)
AVERAGEIFS(scores,dates">="&01/01/2017,dates"<="&31/12/2017)
IF(AND(dates(>=01/01/2017,<=31/12/2017),AVERAGE(scores)))
These all come up with errors, I have also tried using =YEAR(2017) as the criteria and this won't work either. I am also not sure if I am being specific enough in these formulas to pick out the correct cells to use in each average based off the criteria of the date in the next cell - is this possible?
Any Suggestions? Thanks
Bookmarks