Formula
Posted by roger on November 01, 2000 11:01 AM
I am in need of a formula to calculate student test grades on an rolling basis dropping the lowest current grade and providing their current grade average. There is an undetermined number of tests.
I would like the students to be able to see there current grade at any time during the semester (average minus lowest grade) Thanks
Posted by Tim Francis-Wright on November 01, 2000 2:21 PM
If a student's grades are in row 3, columns A through J, for example, you could use the following
formula at K3 (and copy downward as needed):
=AVERAGE(LARGE($A3:J3,ROW(INDIRECT("1:"&MAX(1,COUNT($A3:J3)-1)))))
(This is an array formula: use control-shift-enter instead of enter.)
This prevents a #REF! error when there is only one grade in the A3:J3 range.
If you will need more than 10 grades for the term, simply change J3 in the formula to the
proper ending cell.
HTH
Posted by AB on November 02, 2000 12:41 PM
I'm thinking the array formula could be bypassed with a less complex standard formula.
=(SUM(A3:J3)-MIN(A3:J3))/(COUNT(A3:J3)-1)
So in the above formula we're just saying the sum of all the values minus the minimum divided by the count of all the values minus 1.
