formula to work out annual averages based of the corresponding date in adjacent column??

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

does your sheet have to be laid out that way? it would be a heck of a lot easier if you had names in column A, scores in column B, Dates in column C and an additional column "test" in column D indicating which test (test 1, test 2, test 3 etc) is referred to

I agree that would be easier, unfortunately we want to have a row per person as we have multiple tests and lots of different measurements to record and want each person's data in one place - please see my attached example

ok, assuming that each person appears only once in their own row, and the test date is always in the column to the right of the test result, this user-defined function might work:

Please Login or Register  to view this content.

basically it looks for every cell in the specified range that contains a date and averages the values in the cells to the left of the dates if the years in the dates are the same as the year in the formula.

for example

=AnnAv(B3:K3,2017)

should return the average of scores for the year 2017 in the range b3:k3

Thank you so much, yes that has worked!
I have some tests where the score is classified and then rated (for example a score of 0-2 = classification of "low" = rating of 1; 3-4 = "moderate" = 2; 5-6 = "high" = 3) so the columns look like this:
| SCORE 1 | CLASSIFICATION 1 | RATING 1 | DATE 1 | SCORE 2 | CLASSIFICATION 2 | RATING 2 | DATE 2 |
I also need to the annual average rating as well as score for these tests:

Please refer to test 2 in my attached "database example 2". I have set out sheet 1 to include columns for Score, Rating, and Date. I have adapted the function to make AnnAv2 where "Ccell.Offset(0,-2).Value" in order to find the average score, and I have used the original AnnAv function in order to find the average rating - this works fine.

Sheet 2 has been set out to represent my actual database; I have included all the columns for Score, Classification, Rating, and Date. I have adapted the function to make AnnAv3 where "Ccell.Offset(0,-3).Value" in order to find the average score, and have used the original AnnAv function in order to find the average rating. The output of these functions all come up with a #VALUE! error. I assume this is because of the inclusion of words in the range. Do you have any suggestions for how I might overcome this error or is the only way around it to take out the classifications?

hi

I have had a quick look and it seems that the formula below is throwing it, but I can't work out why.

=IF(U3<>"",IF(U3<=2,"low", IF(AND(U3>2,U3<=4),"moderate",IF(AND(U3>4,U3<=6),"high",""))))

I have commitments all day tomorrow but will try to look at it again on the weekend

No worries, thank you so much for your help