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

ea2146

New Member
Joined
Apr 9, 2012
Messages
27
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!!!

StudentDate of TestTest ScoreAverage Previous Test ScoreHighest Previous Test Score
12/10/201598NANA
13/12/2015729898
14/9/2015798598
15/15/2015888398
16/2/20158084.2598
23/8/201593NANA
24/2/2015719393
25/1/2015898293
26/5/20158884.3333333393
27/15/20156785.2593
33/2/201576NANA
34/6/2015987676
35/24/2015978798
36/3/20156790.3333333398
37/18/20157584.598
41/14/201574NANA
42/12/2015897474
43/23/20159981.589
44/14/20156587.3333333399
45/21/20158781.7599

<tbody>
</tbody>
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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
Joined
Apr 9, 2012
Messages
27
Thank you so much, XOR LX!!

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

Forum statistics

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

Some videos you may like

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...
Top