Excel School Grading Template

[Jake]

I am using a Microsoft website Excel template "Gradebook
based on points". When a student is absent and does not
take the test, but other students do, what do I enter in
the absent student's cell to prevent that null from
calculating an incorrect average? For example is Student
A took 3 of 3 tests, the average grade calculate
correctly; if Student B took only 2 of 3 tests and will
not be penalized, the average still calculates using 3
tests and assumes a blank cell as 0, unfarily lowering
Student B's average.
On a similar note, on a series of 10 quizzes I want to
drop the lowest grade, which creates the same problem: I
want to exclude that dropped grade (Excel cell) from the
averaging, but the program will not allow me to do this.
Help!!

 

[Andy Brown]

I am using a Microsoft website Excel template "Gradebook

based on points".

It might help if you post a link and/or give example(s) of the formula(s)
being used.

AFAIK, the AVERAGE function allows (ignores) blanks. Help says "empty cells
are not counted, but zero values are". This seemingly extends to text, thus

=AVERAGE(B22)

returns 50 for 50, 50, 50 ; 50 for 50, blank, 50 ; 50 for 50, DNA, 50 ;
*but* 33.3 for 50, 0, 50. And NB "N/A" works but "#N/A" crashes.

I don't get the second question.

HTH,
Andy

 

[jaf]

Hi Jake,
Do you have a link to the template?
As Andy said the average function ignores blanks.

--
John
johnf 202 at hotmail dot com


| I am using a Microsoft website Excel template "Gradebook
| based on points". When a student is absent and does not
| take the test, but other students do, what do I enter in
| the absent student's cell to prevent that null from
| calculating an incorrect average? For example is Student
| A took 3 of 3 tests, the average grade calculate
| correctly; if Student B took only 2 of 3 tests and will
| not be penalized, the average still calculates using 3
| tests and assumes a blank cell as 0, unfarily lowering
| Student B's average.
| On a similar note, on a series of 10 quizzes I want to
| drop the lowest grade, which creates the same problem: I
| want to exclude that dropped grade (Excel cell) from the
| averaging, but the program will not allow me to do this.
| Help!!