Finding next and previous values specific to the ID field

[surendarmani]

Hi,

My dataset has >13500 rows. I have already generated this dataset for students who have taken more than 5 courses per semester. Now, Each row represents a student who has taken at least 1 course per semester, which is also known as the academic period(202110 - Fall 2021; 202220-Spring 2021).

Active_UG_Students_with_GPA_by_Term_v3__with_sorting_edit_in_progress.xlsx
	A	B	C	D	E	F	G	H
2	101	Student1	201920	2.52941177	No Previous Data			No Next Data
3	101	Student1	202010	2.75	No Previous Data			No Next Data
4	101	Student1	202110	3.4	No Previous Data			No Next Data
5	101	Student1	202120	2.4	No Previous Data			No Next Data
6	102	Student2	202110	4.	No Previous Data			No Next Data
7	102	Student2	202120	4.	No Previous Data			No Next Data
8	102	Student2	202210	4.	No Previous Data			No Next Data
9	103	Student3	201920	3.	No Previous Data			No Next Data
10	103	Student3	202010	3.5	No Previous Data			No Next Data
11	104	Student4	201920	3.25	No Previous Data			No Next Data
12	104	Student4	202010	3.75	No Previous Data			No Next Data
13	104	Student4	202110	4.	No Previous Data			No Next Data
14	104	Student4	202120	3.5	No Previous Data			No Next Data
15	105	Student5	201920	1.75	No Previous Data			No Next Data
Any_semesters_with_valid_GP (2

Cell Formulas
Range	Formula
E2:E15	E2	=IFERROR(1/(1/SUMIFS(C:C,$A:$A,$A2,$C:$C,$C2-1)),"No Previous Data")
H2:H15	H2	=IFERROR(1/(1/SUMIFS(F:F,$A:$A,$A2,$C:$C,$C2+1)),"No Next Data")

Named Ranges
Name	Refers To	Cells
'Any_semesters_with_valid_GP (2'!_FilterDatabase	='Any_semesters_with_valid_GP (2'!$A$1:$H$13860	H2:H15, E2:E15

My goal of this project is to find an average previous and next term GPA for this population and see whether allowing students to take overloads(5 or more courses per semester) is a good thing or not. Comparing the average GPA of the previous term to overload and next term to overload and evaluating the impact on the student learning curve. So, if the student has taken a course next term and has a row, I want to print that GPA in the appropriate column. If not, I want to print,"no value found".

 

[surendarmani]

Hi,

My dataset has >13500 rows. I have already generated this dataset for students who have taken more than 5 courses per semester. Now, Each row represents a student who has taken at least 1 course per semester, which is also known as the academic period(202110 - Fall 2021; 202220-Spring 2021).

Active_UG_Students_with_GPA_by_Term_v3__with_sorting_edit_in_progress.xlsx
	A	B	C	D	E	F	G	H
2	101	Student1	201920	2.52941177	No Previous Data			No Next Data
3	101	Student1	202010	2.75	No Previous Data			No Next Data
4	101	Student1	202110	3.4	No Previous Data			No Next Data
5	101	Student1	202120	2.4	No Previous Data			No Next Data
6	102	Student2	202110	4.	No Previous Data			No Next Data
7	102	Student2	202120	4.	No Previous Data			No Next Data
8	102	Student2	202210	4.	No Previous Data			No Next Data
9	103	Student3	201920	3.	No Previous Data			No Next Data
10	103	Student3	202010	3.5	No Previous Data			No Next Data
11	104	Student4	201920	3.25	No Previous Data			No Next Data
12	104	Student4	202010	3.75	No Previous Data			No Next Data
13	104	Student4	202110	4.	No Previous Data			No Next Data
14	104	Student4	202120	3.5	No Previous Data			No Next Data
15	105	Student5	201920	1.75	No Previous Data			No Next Data
Any_semesters_with_valid_GP (2

Cell Formulas
Range	Formula
E2:E15	E2	=IFERROR(1/(1/SUMIFS(C:C,$A:$A,$A2,$C:$C,$C2-1)),"No Previous Data")
H2:H15	H2	=IFERROR(1/(1/SUMIFS(F:F,$A:$A,$A2,$C:$C,$C2+1)),"No Next Data")

Named Ranges
Name	Refers To	Cells
'Any_semesters_with_valid_GP (2'!_FilterDatabase	='Any_semesters_with_valid_GP (2'!$A$1:$H$13860	H2:H15, E2:E15

My goal of this project is to find an average previous and next term GPA for this population and see whether allowing students to take overloads(5 or more courses per semester) is a good thing or not. Comparing the average GPA of the previous term to overload and next term to overload and evaluating the impact on the student learning curve. So, if the student has taken a course next term and has a row, I want to print that GPA in the appropriate column. If not, I want to print,"no value found".

Next Academic Period/ GPA - represents the next active semester that the student took courses and secured some grades.
Previous Academic Period/ GPA - represents the previous active semester that the student took courses and secured some grades with GPA calculated.

 

[surendarmani]

Hi,

Just following up to see whether there is a method to find those previous and next semester field values.

Thanks

 

[jtakw]

Hi,

If I understand correctly.
Shouldn't you be SUMming Column D for your Column E formula ? (currently, you sum column C)
And, you're subtracting 1 (-1) from Column C in your Column E formula, shouldn't it be -10 (ten) ?
Same applies to your Column H formula, you sum Column F, which is Blank, I assume you want Also Column D, change/adjust accordingly, and I used +10.

Book3.xlsx
	A	B	C	D	E	F	G	H
1
2	101	Student1	201920	2.529412	No Previous Data			No Next Data
3	101	Student1	202010	2.75	No Previous Data			No Next Data
4	101	Student1	202110	3.4	No Previous Data			2.4
5	101	Student1	202120	2.4	3.4			No Next Data
6	102	Student2	202110	4	No Previous Data			4
7	102	Student2	202120	4	4			No Next Data
8	102	Student2	202210	4	No Previous Data			No Next Data
9	103	Student3	201920	3	No Previous Data			No Next Data
10	103	Student3	202010	3.5	No Previous Data			No Next Data
11	104	Student4	201920	3.25	No Previous Data			No Next Data
12	104	Student4	202010	3.75	No Previous Data			No Next Data
13	104	Student4	202110	4	No Previous Data			3.5
14	104	Student4	202120	3.5	4			No Next Data
15	105	Student5	201920	1.75	No Previous Data			No Next Data
Sheet1073

Cell Formulas
Range	Formula
E2:E15	E2	=IFERROR(1/(1/SUMIFS(D:D,$A:$A,$A2,$C:$C,$C2-10)),"No Previous Data")
H2:H15	H2	=IFERROR(1/(1/SUMIFS(D:D,$A:$A,$A2,$C:$C,$C2+10)),"No Next Data")

 

[surendarmani]

Thanks much for your response. The next and previous academic period is sequential.
These would be sequence:
201920, 201930, 202010, 202020, 202030, 202110 (To avoid this confusion on sequence, I've now created custom sorting in the report)

Students do not necessarily take courses every semester and so don't need to follow the sequence They can skip a semester or two in between. For example Student1 took courses and secured GPA of 2.75 on 202010, but he skipped 202020 and 202030 and directly took courses on 202110 to get 3.4 GPA which would be his next semester GPA.

In this case, Student1(row 3) will have 2.529412 as the previous GPA and 3.4 as the next GPA.

Please let me know if you have any questions.

Appreciate your time and assistance.