Excluding a cell from an average

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I'm working on creating a grade book, but I've encountered a problem with excluding an assignment for individual students when averaging the grades.

I have several different worksheets for different subjects. On each sheet the students are listed on the left (A2:A26), the assignments/quizzes are at the top (B1:Z1), and the value of each assignment is at the bottom (B27:Z27).

I also have a "Results" page with the average mark (percent) for each student, for each subject. The formula I've used on the "Results" page is as follows (as an example):

=SUM(Sheet1!B2:Z2)/SUM(Sheet1!$B$27:$Z$27)

This all works fine. However, what I'm looking to do is have Excel ignore a cell (and the corresponding assignment value) when computing the percentage, IF an "X" is entered instead of a grade.

If I leave the cell blank, the percentage will calculate as if there was a 0 grade in that cell; instead I want the program to essentially "skip" that cell (and the assignment value) for that particular student when an "X" is entered, and calculate the percentage properly.

Any suggestions?

Thanks in advance.

wmd
.

Relevant Pages

  • Re: Student Assignment
    ... They need to write a class file that goes with ... The final grade is determined by the sum of ... I've noticed the calculus students ... Assignment is already past due. ...
    (comp.lang.java.programmer)
  • RE: convert colums to rows
    ... "pack" wrote: ... records the student name, date, assignment, and grade. ... column 2 is the students 1st grade, ...
    (microsoft.public.access.reports)
  • Re: Excluding a cell from an average
    ... and dividing by total assignment value. ... with excluding an assignment for individual students when averaging the ... I also have a "Results" page with the average mark for each ... If I leave the cell blank, the percentage will calculate as if there was ...
    (microsoft.public.excel)
  • Re: Help, my english is getting worst!
    ... The Best News Story (instructor is the author)... ... papers turned in and students are to do the same for this assignment. ... I liked my Grade 11 teacher, Ms Febbraro, my Grade 12 teacher wasn't ...
    (alt.usage.english)
  • Re: Create a formula
    ... Then enter the following formula in cell O2: ... and copy it down the column for all students. ... cell O2 is 1.5 too high if a student never received a grade that ... Column P now contains the final point totals. ...
    (microsoft.public.excel.misc)