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

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]

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