Excluding a cell from an average

Tech-Archive recommends: Speed Up your PC by fixing your registry

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 *** 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
.

Quantcast