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
None of your examples of LINEST is correct. See below for details.
-----
First, it is always bests to attach an Excel file that demonstrates the problem. That is out of respect for those who want to help; do not force them to re-enter your data and formulas. And it might result in a more accurate response; we might not be able to reproduce your results because the posted data is rounded.
"If a picture is worth a 1000 words, an Excel file is worth 1000 pictures". (wink)
But when you post images, always include row numbers as well as column names. I had to infer the row numbers from the cell references in your text.
-----
Second, it is always wise to copy formulas from the Formula Bar and paste them into your posting, instead of retyping them.
I was unable to reproduce your results because you posted LINEST formulas of the form =LINEST(U21:U36, V21:V36...).
But then I looked at "x" and "y" in U20 and V20, and I guessed that the actual formulas are of the form =LINEST(V21:V36, U21:U36....). That is, switching the U and V references. Now my results for "a", "b" and "c" appear to be similar to yours, to wit (see the attached Excel file for details):
-----
Finally, none of your examples of LINEST is correct.
The correct usage is demonstrated in AB21:AD25. With that range selected, enter the formula =LINEST(V21:V36, U21:U36^{1,2}, 1, TRUE)
Note: The formula must be array-entered (press ctrl+shift+Enter instead of just Enter) in versions of Excel before Excel 365 and Excel 2016.
Then the coefficients "a", "b" and "c" are in AB21:AD21. And r^2 is in AB23.
RSQ can also be calculated with the formula =RSQ(V21:V36, W21:W36) in W38. W21:W36 ("est y") is based on formulas of the form (W21) =SERIESSUM(U21, 2, -1, $AB$21:$AD$21)
Aside.... When displaying trendline formulas in charts, it is prudent to increase the number of decimal places by clicking the trendline, then Format Trendline Label. For example, note that the coefficient "a" is more accurately -6.94843E-08, not -7E-08.
-----
PS.... Although I recommend the array-entered LINEST formula above -- it is more efficient -- you can calculate the individual coefficients and r^2 with the following non-array formulas:
a: =INDEX(LINEST($V$21:$V$36, $U$21:$U$36^{1,2}, 1, TRUE), 1, 1)
b: =INDEX(LINEST($V$21:$V$36, $U$21:$U$36^{1,2}, 1, TRUE), 1, 2)
c: =INDEX(LINEST($V$21:$V$36, $U$21:$U$36^{1,2}, 1, TRUE), 1, 3)
r^2: =INDEX(LINEST($V$21:$V$36, $U$21:$U$36^{1,2}, 1, TRUE), 3, 1)
2 Replies
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
