Forum Discussion
Solved
Calculating a running average grade
Hi
I am a teacher and want to add to my student record sheet a running average grade. I would like to set it up so that the average updates throughout the year when more test data is added. However this means that there will be missing data, as students will not have completed all the tests until the end of the year.
This is my data. The different written tests have different grade boundaries and the grades shown are populated by different vlookups based on the % scored in the test.
As they have different boundaries, my idea was to assign each grade with a point score, lookup this point score with a vlookup, then average these point scores before looking up a grade.
I have started with this formulae (not quite got to the stage to look up the final grade). Im not sure how to make larger sorry
I have tried using If functions to solve the problem of missing data but as soon as there is missing data i get a #VALUE! error
Is there anyway to get around this and work out the average with these gaps?
Thanks
This seems to return the intended result in my sample file.
=SUM( IF(E2="",0,VLOOKUP(G2,$R$2:$S$22,2,FALSE)), IF(H2="",0,VLOOKUP(J2,$R$2:$S$22,2,FALSE)), IF(K2="",0,VLOOKUP(M2,$R$2:$S$22,2,FALSE)), IF(N2="",0,VLOOKUP(P2,$R$2:$S$22,2,FALSE)))/ COUNT(E2,H2,K2,N2)
This seems to return the intended result in my sample file.
=SUM( IF(E2="",0,VLOOKUP(G2,$R$2:$S$22,2,FALSE)), IF(H2="",0,VLOOKUP(J2,$R$2:$S$22,2,FALSE)), IF(K2="",0,VLOOKUP(M2,$R$2:$S$22,2,FALSE)), IF(N2="",0,VLOOKUP(P2,$R$2:$S$22,2,FALSE)))/ COUNT(E2,H2,K2,N2)Thats wonderfull thank you
