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.

 

[KRice]

Okay…in the instance where a Retest B becomes necessary, where is that score entered? Is a new column added to the table to accommodate retest B or is the score inserted in the existing retest column?

 

[SFCChase]

Okay…in the instance where a Retest B becomes necessary, where is that score entered? Is a new column added to the table to accommodate retest B or is the score inserted in the existing retest column?

The training developers end up going in after the fact and moving the original failure (retest A) into the initial column (by this point the item analysis has been completed) and the retest B score is entered into retest A (if passed....if failed, the original failing retest A stays in place and the student is relieved).

I know it's not the best system, but it was the best I could come up with.

 

[KRice]

That's okay...and thanks. I was mainly interested in understanding if the table structure remained consistent. That is, each exam requires exactly three columns. That keeps your options open in terms of using formulas that hardwire specific cell addresses or formulas that treat the entire row as an array and then use some additional logic to identify only the columns of interest within the array.

Does the number of exams ever change?

In the discussion with @igold, a key point concerned what the workflow looks like. Specifically, is the table initially prepopulated with the IF(AND formula in all "exam # score" columns? If so, then you will have 0's in all of those columns before any actual scores are entered.

 

[igold]

@KRice, I was not inclined to get into all of the OP's pasted formulas especially when I saw at the bottom of the post that this formula was incorrect

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

in that Y2 as omitted for the eighth exam and his table was only showing what would have been columns up to Column Q, yet he included S2 and V2. I assumed for the sake of brevity he posted like that but in actuality he would extend the grid out to 8 exams. All that being said combined with the incorrect formula being shown, I perhaps took the easy way out by providing a formula that answered the main question

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?

Kudos to you for taking the time to run down all the other formulas. In my Post #5, that mockup worked to answer his question. He is lucky you took the time to tie all his formulas together.

 

[SFCChase]

That's okay...and thanks. I was mainly interested in understanding if the table structure remained consistent. That is, each exam requires exactly three columns. That keeps your options open in terms of using formulas that hardwire specific cell addresses or formulas that treat the entire row as an array and then use some additional logic to identify only the columns of interest within the array.

Does the number of exams ever change?

In the discussion with @igold, a key point concerned what the workflow looks like. Specifically, is the table initially prepopulated with the IF(AND formula in all "exam # score" columns? If so, then you will have 0's in all of those columns before any actual scores are entered.

The structure of the entire workbook does remain consistent throughout the program. The course is broken up into 2 phases but there are 11 exams in phase I and 18 exams in Phase II. I have the workbook preloaded with formulas and a blank copy gets opened for each new class.

By the time i finish this thing they'll decide to alter the number of exams I'm sure.

 

[KRice]

Good point, @igold about the AVERAGE formula. Even if all of the relevant cells were included in AVERAGE, there still remains the issue about excluding 0's from consideration. Based on the OP's latest clarifications, it sounds as if "internal" 0's are not an issue (i.e., what should otherwise be considered a legitimate 0 score because the student completely avoided the exam and retest opportunities and then took subsequent exams that produced non-zero scores). If that's not realistic because of the "relief" measures described by the OP, then the only 0's that need to be excluded from counts are those representing exams not yet taken (assuming the IF(AND formula is pre-populated in the "exam # score" columns).

To make it clear how many actual scores are being considered, I still think it makes sense to include a helper column showing the number of exams scored (see post #7, the AC2 formula) which determines the last scored exam. That value is used by my formulas and could be used in place of the COUNT construction in the offering from @igold to make that approach more robust and avoid counting 0's.

By the time i finish this thing they'll decide to alter the number of exams I'm sure.

@SFCChase...this wouldn't surprise me. For that reason, I would consider an array approach where practical. The formulas look messier, but you will avoid hardwiring numerous single cell references in the formulas. And if you expand or collapse the table size by inserting/deleting three columns at a time between the last two 3-column blocks, the formulas will automatically adjust...so no editing would be necessary for the formulas in Failures, GPA, Best GPA, and Last Exam. You'll still need to paste/edit the headers (unless you want to automatically label those with a formula) and the IF(AND formulas will need to be pasted in the "exam # score" columns. Of all of these, converting the Failures formula to use arrays was the most challenging because of the COUNTIFS components. I think I finally arrived at something that works. In the mini-sheet below, I've expanded the table to confirm that the formulas dynamically adjust to the new size and I've added formulas for the column headings so if the table changes in size, the headings should relabel themselves. With an older version of Excel, some of these formulas may need to be entered with Ctrl-Shift-Enter rather than Enter, and if done successfully, you should see curly brackets automatically surround the formula. Try this where the formulas do not appear to produce the expected results.

Let me know if you have any questions or encounter any issues.

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	AH	AI
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	Exam 9	Retest 9	Exam 9 Score	Exam 10	Retest 10	Exam 10 Score	Failures	GPA	Best GPA	Latest Exam
2	Adams	100		100	100		100	100		100	100		100			0		100	100			0		75	0			0			0	1	62.5	70.0	8
3	Davis	76		76	74	80	74		80	80	70	76	70	71	75	0		75	0	71	76	71		75	0		75	0			0	7	41.2	47.1	9
4	Smith	20	80	20	30	80	30	76		76	74	80	74	72	76	72			0			0			0			0			0	4	54.4	77.2	5
SFCChase

Cell Formulas
Range	Formula
B1:AE1	B1	=CHOOSE(MOD(COLUMN($B$1:$AE$1)-COLUMN($B$1),3)+1,"Exam "&INT((COLUMN($B$1:$AE$1)-COLUMN($B$1))/3)+1,"Retest "& INT((COLUMN($B$1:$AE$1)-COLUMN($B$1))/3)+1,"Exam "& INT((COLUMN($B$1:$AE$1)-COLUMN($B$1))/3)+1 & " Score")
AE2:AE4,AB2:AB4,Y2:Y4,V2:V4,S2:S4,P2:P4,M2:M4,J2:J4,G2:G4,D2:D4	AE2	=IF(AC2>=76,AC2,IF(AND(AC2="",AD2>=76),AD2,IF(AND(AC2<76,AD2>=76),AC2,0)))
AF2:AF4	AF2	=SUM(ISNUMBER($B2:$AE2)*($B2:$AE2<76)*(MOD(COLUMN($B$1:$AE$1)-COLUMN($B$1),3)+1=1)) + SUM(IF(($B2:$AE2="")*(MOD(COLUMN($B$1:$AE$1)-COLUMN($B$1),3)+1=1),IF(ISNUMBER(INDEX($B2:$AE2,,COLUMN($B2:$AE2)-COLUMN($B2)+2)),IF(INDEX($B2:$AE2,,COLUMN($B2:$AE2)-COLUMN($B2)+2)<76,1,0))))
AG2:AG4	AG2	=SUMPRODUCT($B2:$AE2,--(MOD(COLUMN($B$1:$AE$1)-COLUMN($B$1),3)+1=3))/$AI2
AH2:AH4	AH2	=($AG2*$AI2+100*(COLUMNS($B$1:$AE$1)/3-$AI2))/(COLUMNS($B$1:$AE$1)/3)
AI2:AI4	AI2	=INT(LOOKUP(2,1/(ISNUMBER($B2:$AE2)*(MOD(COLUMN($B$1:$AE$1)-COLUMN($B$1),3)+1<>3)),COLUMN($B$1:$AE$1)-COLUMN($B$1)+1)/3)+1
Dynamic array formulas.