Grades Calculation

[Lyndie]

Hi! I am an elementary school teacher trying to calculate
grades in an Excel worksheet. Following is a sample of
my efforts:
Points 10 25 15
Average
=SUM(B2:F2)/SUM($B$1:$F$1)
Name 10 22 15 94%
John 9 25 68%
Mary 10 19 13 84%
Sally 7 21 15 86%
Joe 0 20 14 68%

Total points available are in the top row. Points earned
for each task are listed with the student name. My
calculation (under Average) works fine if all grades are
there or when a student earns a 0. However, if a student
is not present and no grade is issued, I don't want it
figured in as part of the grade. (For example John--I
only want to divide by points for the first two tasks
since he was absent for the third.) Can you make any
suggestions? Thanks, Lyndie

 

[Keith]

Lyndie;

I quickly (but manually) made a change -- knowing that
you will have to do this a few more times -- by
placing the 'denominator' in parentheses and subtracting
the missing score. I typed the ()'s and the minus sign
but clicked the total score cell I wanted to take off
this student's average.

It looks like this:

=SUM(J52:L52)/(SUM($J$50:$L$50)-L50)

You can continue to tack on other missing scores - that
looks like this:

=SUM(J52:L52)/(SUM($J$50:$L$50)-L50-K50)

Excel will give you an 'inconsistent formula' warning -
tell it to leave you alone. Just be careful you
don't 'fill down' over it in the future.

Hope that helps...

 

[Dan E]

Lyndie,

Instead of using a 2 sums you can use a sumproduct and
a sum...

=SUM(B2:F2)/SUMPRODUCT((B2:F2<>"")*($B$1:$F$1))

Translation
=Sum of grades from B2 to F2 divided by the sum of the
values in B1 to F1 which have a corresponding value in
B2 to F2.

Dan E

 

[wow_superb]

Righteous answer, my man.

-----Original Message-----
Lyndie,

Instead of using a 2 sums you can use a sumproduct and
a sum...

=SUM(B2:F2)/SUMPRODUCT((B2:F2<>"")*($B$1:$F$1))

Translation
=Sum of grades from B2 to F2 divided by the sum of the
values in B1 to F1 which have a corresponding value in
B2 to F2.

Dan E




.

 

[Lyndie]

Dan--Just what I was looking for. Many thanks. Lyndie