only including non-empty cells in the formula

Hi there,

I'm a teacher and I prepared a worksheet in which I keep track of the kids' marks during a year.
Every test has a weight (row 3). In cell AA3 there is the total weight of all the given tests. I adjust the total weight after every test.
In the last column I want the worksheet to show the average mark of each student. So the sum of every test mark multiplied by its corresponding weight divided by the total weight of the tests.

This is my formula in the last column:
=($H$3*H4+$I$3*I4+$J$3*J4+$K$3*K4+$L$3*L4+$M$3*M4+$N$3*N4+$O$3*O4+$P$3*P4+$Q$3*Q4+$R$3*R4+$S$3*S4+$T$3*T4+$U$3*U4+$V$3*V4+$W$3*W4+$X$3*X4+$Y$3*Y4+$Z$3*Z4)/$AA$3


But not all the prepared test columns are or will be filled with marks, as there are probably less tests in a year. So, I only want to include the non-empty columns in calculating the average mark.... Can anyone help me with that?

Thanks!
Jeroen

You probably shall use SUMPRODUCT
check something like:

Please Login or Register  to view this content.

PS. Worth noticing suggestion in http://www.excelforum.com/forum-rule...rum-rules.html

Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

Thanks, it works! Very simple...

Sorry for posting the forumula. I thought it was more straight-forward than sending the whole workmap.

Cheers,
Jeroen