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

[ea2146]

Hello,

Thank you in advance for your help! I REALLY appreciate it!

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!!!

<tbody>
[TD="class: xl63"]Student[/TD]
[TD="class: xl64"]Date of Test[/TD]
[TD="class: xl63, width: 90"]Test Score[/TD]
[TD="class: xl63, width: 185"]Average Previous Test Score[/TD]
[TD="class: xl63, width: 181"]Highest Previous Test Score[/TD]

[TD="class: xl63"]1[/TD]
[TD="class: xl64"]2/10/2015[/TD]
[TD="class: xl63"]98[/TD]
[TD="class: xl63"]NA[/TD]
[TD="class: xl63"]NA[/TD]

[TD="class: xl63"]1[/TD]
[TD="class: xl64"]3/12/2015[/TD]
[TD="class: xl63"]72[/TD]
[TD="class: xl63"]98[/TD]
[TD="class: xl63"]98[/TD]

[TD="class: xl63"]1[/TD]
[TD="class: xl64"]4/9/2015[/TD]
[TD="class: xl63"]79[/TD]
[TD="class: xl63"]85[/TD]
[TD="class: xl63"]98[/TD]

[TD="class: xl63"]1[/TD]
[TD="class: xl64"]5/15/2015[/TD]
[TD="class: xl63"]88[/TD]
[TD="class: xl63"]83[/TD]
[TD="class: xl63"]98[/TD]

[TD="class: xl63"]1[/TD]
[TD="class: xl64"]6/2/2015[/TD]
[TD="class: xl63"]80[/TD]
[TD="class: xl63"]84.25[/TD]
[TD="class: xl63"]98[/TD]

[TD="class: xl63"]2[/TD]
[TD="class: xl64"]3/8/2015[/TD]
[TD="class: xl63"]93[/TD]
[TD="class: xl63"]NA[/TD]
[TD="class: xl63"]NA[/TD]

[TD="class: xl63"]2[/TD]
[TD="class: xl64"]4/2/2015[/TD]
[TD="class: xl63"]71[/TD]
[TD="class: xl63"]93[/TD]
[TD="class: xl63"]93[/TD]

[TD="class: xl63"]2[/TD]
[TD="class: xl64"]5/1/2015[/TD]
[TD="class: xl63"]89[/TD]
[TD="class: xl63"]82[/TD]
[TD="class: xl63"]93[/TD]

[TD="class: xl63"]2[/TD]
[TD="class: xl64"]6/5/2015[/TD]
[TD="class: xl63"]88[/TD]
[TD="class: xl63"]84.33333333[/TD]
[TD="class: xl63"]93[/TD]

[TD="class: xl63"]2[/TD]
[TD="class: xl64"]7/15/2015[/TD]
[TD="class: xl63"]67[/TD]
[TD="class: xl63"]85.25[/TD]
[TD="class: xl63"]93[/TD]

[TD="class: xl63"]3[/TD]
[TD="class: xl64"]3/2/2015[/TD]
[TD="class: xl63"]76[/TD]
[TD="class: xl63"]NA[/TD]
[TD="class: xl63"]NA[/TD]

[TD="class: xl63"]3[/TD]
[TD="class: xl64"]4/6/2015[/TD]
[TD="class: xl63"]98[/TD]
[TD="class: xl63"]76[/TD]
[TD="class: xl63"]76[/TD]

[TD="class: xl63"]3[/TD]
[TD="class: xl64"]5/24/2015[/TD]
[TD="class: xl63"]97[/TD]
[TD="class: xl63"]87[/TD]
[TD="class: xl63"]98[/TD]

[TD="class: xl63"]3[/TD]
[TD="class: xl64"]6/3/2015[/TD]
[TD="class: xl63"]67[/TD]
[TD="class: xl63"]90.33333333[/TD]
[TD="class: xl63"]98[/TD]

[TD="class: xl63"]3[/TD]
[TD="class: xl64"]7/18/2015[/TD]
[TD="class: xl63"]75[/TD]
[TD="class: xl63"]84.5[/TD]
[TD="class: xl63"]98[/TD]

[TD="class: xl63"]4[/TD]
[TD="class: xl64"]1/14/2015[/TD]
[TD="class: xl63"]74[/TD]
[TD="class: xl63"]NA[/TD]
[TD="class: xl63"]NA[/TD]

[TD="class: xl63"]4[/TD]
[TD="class: xl64"]2/12/2015[/TD]
[TD="class: xl63"]89[/TD]
[TD="class: xl63"]74[/TD]
[TD="class: xl63"]74[/TD]

[TD="class: xl63"]4[/TD]
[TD="class: xl64"]3/23/2015[/TD]
[TD="class: xl63"]99[/TD]
[TD="class: xl63"]81.5[/TD]
[TD="class: xl63"]89[/TD]

[TD="class: xl63"]4[/TD]
[TD="class: xl64"]4/14/2015[/TD]
[TD="class: xl63"]65[/TD]
[TD="class: xl63"]87.33333333[/TD]
[TD="class: xl63"]99[/TD]

[TD="class: xl63"]4[/TD]
[TD="class: xl64"]5/21/2015[/TD]
[TD="class: xl63"]87[/TD]
[TD="class: xl63"]81.75[/TD]
[TD="class: xl63"]99[/TD]

</tbody>

 

[XOR LX]

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]

Thank you so much, XOR LX!!

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

 

[XOR LX]

No worries! Glad to help.

Cheers