Average Weighted Grades with Blank Cells

Hi, all! I'm trying to create a spreadsheet for my wife to easily average her 3rd grade class scores. As you can see from my example (below) students might miss a test due to an excused absence, etc., and these are simply left blank and not to be averaged into the overall score (although some test WILL be made up). Also, she typically drops the lowest score for a given semester by just "deleting" that score. So--I need for excel to ignore any blank cells and not include them in the average. Typically, this wouldn't be very much of a problem by just using the excel "average" command since it ignore blank cells. However...

My wife is now using weighted test scores (as you can see, designated 1, 2 or 3), where a "1" counts the score one time in the average, "2" is counted twice in the overall average, and so-on. I found the "sumproduct" command for weighted averages, and it works great... if there are no blanks. The bad thing is, even if it ignored a blank score cell it still counts the "weight" column and thus lowers the score even more. The formula I was using was :

=SUMPRODUCT(D4:R4,D$3:R$3)/SUM(D$3:R$3)

I could go very basic and just enter each score the number of times it is counted, and get rid of the "weight" column. I did this initially, but since my wife isn't very fond of excel in the first place she felt it to be cumbersome.

In short, I would like for the spreadsheet to be set up exactly as shown, but when creating the average I would like for it to ignore any blank cell when calculating the average of the scores. Any help would be greatly appreciated!!!

Below is the screen capture:

Capture2.jpg

Daddylonglegs--

Wow! I can't believe it was that simple. Thanks so much!!!

First off, I found this thread really helpful. So thank you!
Also I should probably be creating this question as a new thread, but all of the right people are involved in this one, and the question remains the same.
I have run into a slightly more complicated situation that I would like some advice for. It is a scoring grid for community proposals.
I have a similar grid, but instead of being rated out of 100, each category is ranked out of five (shown in blue). On top of this, within one of the weightings there is the possibility of varying the sub-weighting based on the number of activities included.

I have used what I have taken from daddylonglegs in this thread and altered it minimally to try to meet the situation. It seems to be working. And I have no idea why! ) Or if it is just giving the appearance of working. Any advice is appreciated.