# Formulas to generate "previous average test score" and "previous max test score" for multiple students

#### ea2146

##### New Member
Hello,

I have thousands of previous test scores for hundreds of students along with the dates of the tests (so I can determine the chronological order of the test scores for each student). I want to create a formula that will calculate each student's previous performance up to the current point in time. Specifically, I need a formula that will automatically calculate the values that are currently in columns D and E of the below table. Column D shows the average of previous scores for each student. For example, in the row that represents the third test date for Student 1, I want the average of Student 1's first two test scores to appear (in the example below, the average of 98+72=85). Column E shows the max of previous scores for each student. For example, in the row that represents the third test date for Student 1, I want the max of Student 1's first two test scores to appear (in the example below, the max of 98 and 72 is 98).

I would greatly appreciate it if someone could provide a formula to calculate the values in Column D and a formula to calculate the values in Column E.

Thanks!!!

 Student Date of Test Test Score Average Previous Test Score Highest Previous Test Score 1 2/10/2015 98 NA NA 1 3/12/2015 72 98 98 1 4/9/2015 79 85 98 1 5/15/2015 88 83 98 1 6/2/2015 80 84.25 98 2 3/8/2015 93 NA NA 2 4/2/2015 71 93 93 2 5/1/2015 89 82 93 2 6/5/2015 88 84.33333333 93 2 7/15/2015 67 85.25 93 3 3/2/2015 76 NA NA 3 4/6/2015 98 76 76 3 5/24/2015 97 87 98 3 6/3/2015 67 90.33333333 98 3 7/18/2015 75 84.5 98 4 1/14/2015 74 NA NA 4 2/12/2015 89 74 74 4 3/23/2015 99 81.5 89 4 4/14/2015 65 87.33333333 99 4 5/21/2015 87 81.75 99

<tbody>
</tbody>

#### XOR LX

##### Well-known Member
Hi.

Assuming you have Excel 2010 or later, and that your table is in A1:G21 (with headers in row 1), in D2:

=IF(COUNTIF(A\$2:A2,A2)=1,"NA",AVERAGEIF(A\$1:A1,A1,C\$1:C1))

In E2:

=IF(D2="NA",D2,AGGREGATE(14,6,C\$1:C1/(A\$1:A1=A1),1))

Copy down as required.

Regards

#### ea2146

##### New Member
Thank you so much, XOR LX!!

This is perfect. I really appreciate you taking the time to help

Cheers

1,081,523
Messages
5,359,263
Members
400,523
Latest member
ExcelNewbie98

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...