Excel 2003 SP3
WinXP Pro SP3

I have a set of tests that I need to average for some students.
In row 7 for columns D through 12 I indicate a Y or an N if I'd like to
include that weeks test/quiz into the final test score. Sometimes I provide
pre-tests and pre-quizes just to determine the level of understanding of the
material. I need to keep these results but I don't want to include them in
the final outcome. Hence, the student's results may be like this:
Column
A B C D E F
Week # 1 2 3 4 5
Student Name
Include? N N Y N Y
Results for 72 64 84 53 89 Average should be (84+89)/2 (2
test w/ Y)
Doe, John
Results for
Smith, Mary 75 79 85 67 79 Average should be (85+79)/2 (2
tests w/ Y)
How can I conditionally SUM and average the above data by including ONLY
those results with a Y in the "Include?" cell of the same column that has the
appropriate results? (I don't want a deep IF-conditional test if at all
possible)
TIA!