Projected GPA

[SFCChase]

I have a rather involved gradebook that gives me running GPA's based off exam scores (exam scores are calculated through initial and retest scores if necessary). I was recently told that we are altering our program and I need to calculate a "best possible" GPA as students progress through the course. Is there a formula that will give me a "best possible GPA" without entering a 100 in for every exam that hasn't been taken?

Due to security protocols I cannot download XL2BB so the table below is an example what I have in place already. Any assistance would be greatly appreciated.

Student	Exam 1	Retest 1	Exam 1 score	Exam 2	Retest 2	Exam 2 score	Exam 3	Retest 3	Exam 3 score	Exam 4	Retest 4	Exam 4 score	Exam 5 score	Exam 6 score	Exam 7 score	Exam 8 score	Failures	GPA	Best GPA
Adams	100		100	100		100	100		100	100		100					0	100	100
Davis	76		76	74	80	74		80	80	70	76	70					2	75	88
Smith	20	80	20	30	80	30	76		76	74	80	74					3	50	75

In the example above all of the exam # score columns have the following formula to calculate a final score (retests must get above a 76 but will maintain original score) =IF($B2>=76,B2,IF(AND(B2="",C2>=76),C2,IF(AND(B2<76,C2>=76),B2,0)))
This searches initial grade and returns score if it's over 76...if it's less than 76 and the retest is over 76 a score of 76 is returned. If there is no initial exam and the retest is over 76 they receive that score. If initial and retest are below 76, they get a zero.

Failures is calculated by the following formula
=COUNTIF(B2,"<76")+COUNTIFS(B2,"",C2,"<76)+COUNTIF(E2,"<76")+COUNTIFS(E2,"",F2,"<76)+COUNTIF(H2,"<76")+COUNTIFS(H2,"",I2,"<76)+COUNTIF(K2,"<76")+COUNTIFS(K2,"",L2,"<76)+COUNTIF(N2,"<76")+COUNTIFS(N2,"",O2,"<76)+COUNTIF(Q2,"<76")+COUNTIFS(Q2,"",R2,"<76)+COUNTIF(T2,"<76")+COUNTIFS(T2,"",U2,"<76)+COUNTIF(W2,"<76")+COUNTIFS(W2,"",X2,"<76)

GPA is calculated by using the following formula
=AVERAGE(D2,G2,J2,M2,P2,S2,V2)

If I can get a formula to calculate BEST GPA (i.e. they get 100 on all future exams) I can project to a point where they are unable to maintain a 76 average (Smith) so that they can be relieved.

 

[igold]

Editing Post

 

[igold]

Assuming eight exams and the Exam 8 Score resides in Cell Y2 then:

=(SUM($D2,$G2,$J2,$M2,$P2,$S2,$V2,$Y2)+(8-(COUNT($D2,$G2,$J2,$M2,$P2,$S2,$V2,$Y2)))*100)/8

 

[KRice]

Here is another approach:

MrExcel_20220808.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Student	Exam 1	Retest 1	Exam 1 score	Exam 2	Retest 2	Exam 2 score	Exam 3	Retest 3	Exam 3 score	Exam 4	Retest 4	Exam 4 score	Exam 5 score	Retest 5	Exam 5 score	Exam 6 score	Retest 6	Exam 6 score	Exam 7 score	Retest 7	Exam 7 score	Exam 8 score	Retest 8	Exam 8 score	Failures	GPA	Best GPA	Latest Exam
2	Adams	100		100	100		100	100		100	100		100			0			0			0			0	0	100	100.0	4
3	Davis	76		76	74	80	74		80	80	70	76	70	71	75	0			0			0			0	3	60	75.0	5
4	Smith	20	80	20	30	80	30	76		76	74	80	74	72	76	72			0			0			0	4	54.4	71.5	5
Sheet8

Cell Formulas
Range	Formula
Y2:Y4,V2:V4,S2:S4,P2:P4,M2:M4,J2:J4,G2:G4,D2:D4	Y2	=IF(W2>=76,W2,IF(X2>=76,(W2="")*X2+(W2<76)*W2,0))
Z2:Z4	Z2	=COUNTIF(B2,"<76")+COUNTIFS(B2,"",C2,"<76")+COUNTIF(E2,"<76")+COUNTIFS(E2,"",F2,"<76")+COUNTIF(H2,"<76")+COUNTIFS(H2,"",I2,"<76")+COUNTIF(K2,"<76")+COUNTIFS(K2,"",L2,"<76")+COUNTIF(N2,"<76")+COUNTIFS(N2,"",O2,"<76")+COUNTIF(Q2,"<76")+COUNTIFS(Q2,"",R2,"<76")+COUNTIF(T2,"<76")+COUNTIFS(T2,"",U2,"<76")+COUNTIF(W2,"<76")+COUNTIFS(W2,"",X2,"<76")
AA2:AA4	AA2	=SUMPRODUCT($B2:$Y2,--(MOD(COLUMN($B$1:$Y$1)-COLUMN($B$1),3)+1=3))/$AC2
AB2:AB4	AB2	=($AA2*$AC2+100*(COLUMNS($B$1:$Y$1)/3-$AC2))/(COLUMNS($B$1:$Y$1)/3)
AC2:AC4	AC2	=MATCH(2,MMULT(--((($B2:$Y2="")*(INT((COLUMN($B$1:$Y$1)-COLUMN($B$1))/3)+1))=ROW($A$1:INDEX($A:$A,COLUMNS($B$1:$Y$1)/3)) ),ROW($A$1:INDEX($A:$A,COLUMNS($B$1:$Y$1)))^0),0)-1

I've tried to make the formulas more robust so that exams can be added or deleted and the formulas will adapt to the new size of the table. One challenge is determining which exam was the last taken. The reason this is a challenge is that you populate the "score" columns with 0's (which will be counted as numbers by the COUNT formula in the post above by @igold), so counting the number of values in the "score" columns isn't reliable. Even if we attempt to filter out the 0's, some errors may occur because your scoring rules assign a legitimate 0 for the score under certain conditions (1st attempt <76 and 2nd attempt is also <76). For that reason, I attempted to determine the last exam based on when the first occasion is found where the "exam x score" and "retest x" columns are blank. But I'm not sure that's perfect. Do you have instances where a student takes exams 1 and 2, completely misses all opportunities to score on exam 3, and then takes exams 4, 5, and so on? My approach would find exam 2 as the latest exam, which would not be correct. But before trying to tackle that problem, I wanted to establish that it can occur.

As a side point, regarding the scoring, I don't follow the rationale behind IF(AND(B2<76,C2>=76),B2,0). There seems to be no incentive for taking a retest because even if one scores better on the retest (C2), they still receive a final exam score of B2 (which was lower than 76).

After determining the Latest Exam, the formulas are fairly straightforward for computing the GPA and the Best GPA.

I was attempting to improve upon the Failures formula, but encountered an issue. Have a look at the yellow and orange cells. The COUNTIF formula is returning 3. Why is the orange cell not counted as the 4th failure?

 

[igold]

@KRice I am finding that your answer is failing. The OP wants to know what the best possible GPA a student can have without having to enter 100 into the columns of the tests that have not yet taken. For your answer to work, that 100 score must be entered in.

I need to calculate a "best possible" GPA as students progress through the course. Is there a formula that will give me a "best possible GPA" without entering a 100 in for every exam that hasn't been taken?

My formula works perfectly. The OP has obviously done this manually and represented that in the last column of the his Post #1

Scores.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Student	Exam 1	Retest 1	Exam 1 score	Exam 2	Retest 2	Exam 2 score	Exam 3	Retest 3	Exam 3 score	Exam 4	Retest 4	Exam 4 score	Exam 5	Retest 5	Exam 5 score	Exam 6	Retest 6	Exam 6 score	Exam 7	Retest 7	Exam 7 score	Exam 8	Retest 8	Exam 8 score	Failures	GPA	Best GPA
2	Adams	100		100	100		100	100		100	100		100													0		100
3	Davis	76		76	74	80	74		80	80	70	76	70													2	75	87.5
4	Smith	20	80	20	30	80	30	76		76	74	80	74													3	50	75
5
6
7
Sheet1

Cell Formulas
Range	Formula
AB2:AB4	AB2	=(SUM($D2,$G2,$J2,$M2,$P2,$S2,$V2,$Y2)+(8-(COUNT($D2,$G2,$J2,$M2,$P2,$S2,$V2,$Y2)))*100)/8

If you keep adding 100 (in the Exam # Score column) to the tests not yet taken the Best GPA does not change. Anything lower than 100 entered will reduce the Best GPA

 

[KRice]

In post #1 the OP states the formula used in the exam # score columns is:

=IF($B2>=76,B2,IF(AND(B2="",C2>=76),C2,IF(AND(B2<76,C2>=76),B2,0)))

That formula results in a 0 under two conditions: 1) either no scores have been entered for the exam and retest, or 2) scores have been entered but they are too low. Your COUNT formula still counts all of the 0's, even those for exams not yet taken. In your example post, you don't have those formulas present in the "exam # score" columns so you don't see this problem. This may be a non-issue if the OP only expands the table after each exam and copies the formula into the newly expanded "exam # score" column, but if the table is initially expanded and the formula is placed in those columns, COUNT will always return 8 even if only 1 or 2 exams have been logged. Whether it is an issue or not, the OP should be aware of it.

For your answer to work, that 100 score must be entered in.

This comment isn't correct. The Best GPA formula in my post is:

=($AA2*$AC2+100*(COLUMNS($B$1:$Y$1)/3-$AC2))/(COLUMNS($B$1:$Y$1)/3)

The first term ($AA2*$AC2) reconstitutes the total sum of exams taken. The second term assumes 100 is scored on each of the remaining yet-to-be-taken exams, so the quantity (100*(COLUMNS($B$1:$Y$1)/3-$AC2)) is added to the previous term. The grand total is then divided by the total number of exams that will eventually by taken (in this case, 8). There is no manual entering of 100 for yet-to-be-taken exams in order to make this calculation.

You can see these points in this example (see the igold columns in blue) compared to my results for the GPA. When the OP's formula is present in the "exam # score" columns, COUNT returns 8 (col. AE), which leads to incorrect Best GPAs in AD. The COUNT needs to return what is shown in column AC where I determine the latest exam taken. If those numbers are used in place of 8 (see col. AF), the correct Best GPA would be returned (as shown in col AG).

MrExcel_20220808.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	Student	Exam 1	Retest 1	Exam 1 score	Exam 2	Retest 2	Exam 2 score	Exam 3	Retest 3	Exam 3 score	Exam 4	Retest 4	Exam 4 score	Exam 5	Retest 5	Exam 5 score	Exam 6	Retest 6	Exam 6 score	Exam 7	Retest 7	Exam 7 score	Exam 8	Retest 8	Exam 8 score	Failures	GPA	Best GPA	Latest Exam	igold GPA	igold COUNT	igold COUNT needs to return	…to yield this Best GPA
2	Adams	100		100	100		100	100		100	100		100			0			0			0			0	0	100	100.0	4	50.0	8	4	100.0
3	Davis	76		76	74	80	74		80	0	70	76	70	71	75	71			0			0			0	3	58.2	73.9	5	36.4	8	5	73.9
4	Smith	20	80	20	30	80	30	76		0	74	80	74	72	76	72			0			0			0	4	39.2	62.0	5	24.5	8	5	62.0
Sheet8 (2)

Cell Formulas
Range	Formula
Y2:Y4,V2:V4,S2:S4,P2:P4,M2:M4,J2:J4,G2:G4,D2:D4	Y2	=IF($B2>=76,W2,IF(AND(W2="",X2>=76),X2,IF(AND(W2<76,X2>=76),W2,0)))
Z2:Z4	Z2	=COUNTIF(B2,"<76")+COUNTIFS(B2,"",C2,"<76")+COUNTIF(E2,"<76")+COUNTIFS(E2,"",F2,"<76")+COUNTIF(H2,"<76")+COUNTIFS(H2,"",I2,"<76")+COUNTIF(K2,"<76")+COUNTIFS(K2,"",L2,"<76")+COUNTIF(N2,"<76")+COUNTIFS(N2,"",O2,"<76")+COUNTIF(Q2,"<76")+COUNTIFS(Q2,"",R2,"<76")+COUNTIF(T2,"<76")+COUNTIFS(T2,"",U2,"<76")+COUNTIF(W2,"<76")+COUNTIFS(W2,"",X2,"<76")
AA2:AA4	AA2	=SUMPRODUCT($B2:$Y2,--(MOD(COLUMN($B$1:$Y$1)-COLUMN($B$1),3)+1=3))/$AC2
AB2:AB4	AB2	=($AA2*$AC2+100*(COLUMNS($B$1:$Y$1)/3-$AC2))/(COLUMNS($B$1:$Y$1)/3)
AC2:AC4	AC2	=IFERROR(MATCH(2,MMULT(--((($B2:$Y2="")*(INT((COLUMN($B$1:$Y$1)-COLUMN($B$1))/3)+1))=ROW($A$1:INDEX($A:$A,COLUMNS($B$1:$Y$1)/3)) ),ROW($A$1:INDEX($A:$A,COLUMNS($B$1:$Y$1)))^0),0)-1,COLUMNS($B$1:$Y$1)/3)
AD2:AD4	AD2	=(SUM($D2,$G2,$J2,$M2,$P2,$S2,$V2,$Y2)+(8-(COUNT($D2,$G2,$J2,$M2,$P2,$S2,$V2,$Y2)))*100)/8
AE2:AE4	AE2	=COUNT($D2,$G2,$J2,$M2,$P2,$S2,$V2,$Y2)
AF2:AF4	AF2	=AC2
AG2:AG4	AG2	=(SUM($D2,$G2,$J2,$M2,$P2,$S2,$V2,$Y2)+(8-(AF2))*100)/8

As I mentioned, whether this is an issue depends on what the initial form of the table looks like and whether the "exam # score" columns are pre-populated with the formulas.

I discovered some typos and an issue in my earlier offering, corrected in this latest example. The typos involved some of the first exam columns, which contained the word "score". The other issue involved an error that occurs when determining the number of the last exam taken...when all exams are taken, the MATCH formula used generates an error becasue there are no more two-blank blocks. An IFERROR wrapper was used to address this in the latest example. But I still suspect something else may be necessary to reliably identify the number of exams taken.

If you keep adding 100 (in the Exam # Score column) to the tests not yet taken the Best GPA does not change. Anything lower than 100 entered will reduce the Best GPA

I don't quite understand your point with this comment. I agree with it. And in my offering, this is what happens with one exception...the same issue I mentioned previously. If you try out my mini-sheet and manually add 100's beginning immediately after the last exam, you'll see the Best GPA does not change (and it shouldn't). But if you skip an exam and add 100's beyond the skipped exam, the Best GPA is not correct because the method used to determine the Latest Exam sees the skipped exam and takes the prior one as the latest. This is why I'd like some clarity on whether completely skipped exams/scores actually happen. If they do, then some method is needed to distinguish that legitimate 0 score from the exams not-yet-taken 0 scores. And if the "exam x score" formula is present initially in all "exam x score" columns, your COUNT approach

 

[KRice]

A slightly better way to express the OP's "exam # score" formula is:

=IF(B2>=76,B2,IF(AND(B2="",C2>=76),C2,IF(AND(B2<76,C2>=76),B2,0)))

...this eliminates the fixed $B so the formula can be copied throughout all "exam # score" columns and still refer to the correct cells immediately to the left.

After thinking about the "Latest Exam" issue I mentioned above, a better way to determine that isn't to look for the first instance of two-blanks in an exam scoring block. Rather it might be preferred to look for the last value in any of those two-cell blocks. This can be done with:

=INT(LOOKUP(2,1/(ISNUMBER($B2:$Y2)*(MOD(COLUMN($B$1:$Y$1)-COLUMN($B$1),3)+1<>3)),COLUMN($B$1:$Y$1)-COLUMN($B$1)+1)/3)+1

This means that any empty two-cell exam/retest blocks that come before other (later) score blocks that are populated will be counted as a legitimate 0 score...and this resolves the issue I described previously. The COUNT component in the offering from @igold could be swapped out for this as well, and that would eliminate potential issues if the "exam # score" columns are pre-populated with 0's. As a matter of good practice, it would make sense to enter an actual "0" for the exam score if an exam and retest are completely missed.

Here's the latest version with the above tweaks. In this example, Adams Exam 5 score of 0 counts (the latest exam is determined to be 6).

MrExcel_20220808.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Student	Exam 1	Retest 1	Exam 1 score	Exam 2	Retest 2	Exam 2 score	Exam 3	Retest 3	Exam 3 score	Exam 4	Retest 4	Exam 4 score	Exam 5	Retest 5	Exam 5 score	Exam 6	Retest 6	Exam 6 score	Exam 7	Retest 7	Exam 7 score	Exam 8	Retest 8	Exam 8 score	Failures	GPA	Best GPA	Latest Exam
2	Adams	100		100	100		100	100		100	100		100			0		100	100			0			0	0	83.3	87.5	6
3	Davis	76		76	74	80	74		80	80	70	76	70	71	75	0			0			0			0	3	60.0	75.0	5
4	Smith	20	80	20	30	80	30	76		76	74	80	74	72	76	72			0			0			0	4	54.4	71.5	5
Sheet8 (2)

Cell Formulas
Range	Formula
Y2:Y4,V2:V4,S2:S4,P2:P4,M2:M4,J2:J4,G2:G4,D2:D4	Y2	=IF(W2>=76,W2,IF(AND(W2="",X2>=76),X2,IF(AND(W2<76,X2>=76),W2,0)))
Z2:Z4	Z2	=COUNTIF(B2,"<76")+COUNTIFS(B2,"",C2,"<76")+COUNTIF(E2,"<76")+COUNTIFS(E2,"",F2,"<76")+COUNTIF(H2,"<76")+COUNTIFS(H2,"",I2,"<76")+COUNTIF(K2,"<76")+COUNTIFS(K2,"",L2,"<76")+COUNTIF(N2,"<76")+COUNTIFS(N2,"",O2,"<76")+COUNTIF(Q2,"<76")+COUNTIFS(Q2,"",R2,"<76")+COUNTIF(T2,"<76")+COUNTIFS(T2,"",U2,"<76")+COUNTIF(W2,"<76")+COUNTIFS(W2,"",X2,"<76")
AA2:AA4	AA2	=SUMPRODUCT($B2:$Y2,--(MOD(COLUMN($B$1:$Y$1)-COLUMN($B$1),3)+1=3))/$AC2
AB2:AB4	AB2	=($AA2*$AC2+100*(COLUMNS($B$1:$Y$1)/3-$AC2))/(COLUMNS($B$1:$Y$1)/3)
AC2:AC4	AC2	=INT(LOOKUP(2,1/(ISNUMBER($B2:$Y2)*(MOD(COLUMN($B$1:$Y$1)-COLUMN($B$1),3)+1<>3)),COLUMN($B$1:$Y$1)-COLUMN($B$1)+1)/3)+1

 

[SFCChase]

Here is another approach:

MrExcel_20220808.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Student	Exam 1	Retest 1	Exam 1 score	Exam 2	Retest 2	Exam 2 score	Exam 3	Retest 3	Exam 3 score	Exam 4	Retest 4	Exam 4 score	Exam 5 score	Retest 5	Exam 5 score	Exam 6 score	Retest 6	Exam 6 score	Exam 7 score	Retest 7	Exam 7 score	Exam 8 score	Retest 8	Exam 8 score	Failures	GPA	Best GPA	Latest Exam
2	Adams	100		100	100		100	100		100	100		100			0			0			0			0	0	100	100.0	4
3	Davis	76		76	74	80	74		80	80	70	76	70	71	75	0			0			0			0	3	60	75.0	5
4	Smith	20	80	20	30	80	30	76		76	74	80	74	72	76	72			0			0			0	4	54.4	71.5	5
Sheet8

Cell Formulas
Range	Formula
Y2:Y4,V2:V4,S2:S4,P2:P4,M2:M4,J2:J4,G2:G4,D2:D4	Y2	=IF(W2>=76,W2,IF(X2>=76,(W2="")*X2+(W2<76)*W2,0))
Z2:Z4	Z2	=COUNTIF(B2,"<76")+COUNTIFS(B2,"",C2,"<76")+COUNTIF(E2,"<76")+COUNTIFS(E2,"",F2,"<76")+COUNTIF(H2,"<76")+COUNTIFS(H2,"",I2,"<76")+COUNTIF(K2,"<76")+COUNTIFS(K2,"",L2,"<76")+COUNTIF(N2,"<76")+COUNTIFS(N2,"",O2,"<76")+COUNTIF(Q2,"<76")+COUNTIFS(Q2,"",R2,"<76")+COUNTIF(T2,"<76")+COUNTIFS(T2,"",U2,"<76")+COUNTIF(W2,"<76")+COUNTIFS(W2,"",X2,"<76")
AA2:AA4	AA2	=SUMPRODUCT($B2:$Y2,--(MOD(COLUMN($B$1:$Y$1)-COLUMN($B$1),3)+1=3))/$AC2
AB2:AB4	AB2	=($AA2*$AC2+100*(COLUMNS($B$1:$Y$1)/3-$AC2))/(COLUMNS($B$1:$Y$1)/3)
AC2:AC4	AC2	=MATCH(2,MMULT(--((($B2:$Y2="")*(INT((COLUMN($B$1:$Y$1)-COLUMN($B$1))/3)+1))=ROW($A$1:INDEX($A:$A,COLUMNS($B$1:$Y$1)/3)) ),ROW($A$1:INDEX($A:$A,COLUMNS($B$1:$Y$1)))^0),0)-1

I've tried to make the formulas more robust so that exams can be added or deleted and the formulas will adapt to the new size of the table. One challenge is determining which exam was the last taken. The reason this is a challenge is that you populate the "score" columns with 0's (which will be counted as numbers by the COUNT formula in the post above by @igold), so counting the number of values in the "score" columns isn't reliable. Even if we attempt to filter out the 0's, some errors may occur because your scoring rules assign a legitimate 0 for the score under certain conditions (1st attempt <76 and 2nd attempt is also <76). For that reason, I attempted to determine the last exam based on when the first occasion is found where the "exam x score" and "retest x" columns are blank. But I'm not sure that's perfect. Do you have instances where a student takes exams 1 and 2, completely misses all opportunities to score on exam 3, and then takes exams 4, 5, and so on? My approach would find exam 2 as the latest exam, which would not be correct. But before trying to tackle that problem, I wanted to establish that it can occur.

As a side point, regarding the scoring, I don't follow the rationale behind IF(AND(B2<76,C2>=76),B2,0). There seems to be no incentive for taking a retest because even if one scores better on the retest (C2), they still receive a final exam score of B2 (which was lower than 76).

After determining the Latest Exam, the formulas are fairly straightforward for computing the GPA and the Best GPA.

I was attempting to improve upon the Failures formula, but encountered an issue. Have a look at the yellow and orange cells. The COUNTIF formula is returning 3. Why is the orange cell not counted as the 4th failure?

Thanks for the assist.

The rationale behind the IF(AND formula because the way the program is built if an initial exam is failed the student must take a retest (which is considered a Go/No-Go exam, but must still score above a 76). If they fail both the initial and the retest they receive a 0 which, using conditional formatting, alerts my training developers to the student meeting instant relief status (relief can be signified by fully failing and exam or having 3 initial exam failures over a phase of training).

As for the orange block not counting as an initial failure, if the student misses an initial exam for some reason (medical, family emergency) they will test with the retest group (taking the retest version) and it counts as an initial score. I can't score it with the initial score because we use overall exam version pass rates and item analysis to review student performance.

 

[KRice]

Thank you for the explanation about the IF(AND formula. Regarding another question I asked about the number of failures, I think I understand why the orange cell isn't counted--there is no exam score there, so COUNTIF ignores it. That leads to another point in the discussion: Are there instances where an exam and retest for the same two-cell block are devoid of any entries? In other words, the exam was completely missed, and later exams are populated with scores?

 

[SFCChase]

Thank you for the explanation about the IF(AND formula. Regarding another question I asked about the number of failures, I think I understand why the orange cell isn't counted--there is no exam score there, so COUNTIF ignores it. That leads to another point in the discussion: Are there instances where an exam and retest for the same two-cell block are devoid of any entries? In other words, the exam was completely missed, and later exams are populated with scores?

There shouldn't be any instances where an exam is devoid of any entries. If it gets to that point, the student has likely been relieved from the program and will get deleted from the gradebook. There are rare occasions where a student will miss an initial exam and then fail the retest version. In that instance the student will end up taking a 3rd version and the training developer will put a 76 (if retest B is passed) or they will put the first failing score (retest version A) in and leave it while the student is processed for relief from the program.