Excel 2007 : sum of divided columns with letters and numbers

I am using excel for monitoring students marks in science.
What I am trying to do (with no excel training) is link columns and rows to generate an overall mark. eg:
10 15 20 total
a 3 0 #VALUE!

a-means the student was absent and the score out od 10 should not be applied.
3 is the score out of 15 and
0 is the score out of 20

the following is as far as I have gotten
=SUM(IF(ISTEXT(A2),"""",IF(A2="0",0,IF(A2>0,A2/A1,))),IF(ISTEXT(B2),"",IF(B2="0",0,IF(B2>0,B2/B1,))),IF(ISTEXT(C2),"",IF(C2="0",0,IF(C2>0,C2/C1,))))

I have stuffed up somewhere but I have no idea where. Any help is greatly appreciated.

chook

Try

Please Login or Register  to view this content.

[EDIT]
Sorry wasn't thinking this is easier

Please Login or Register  to view this content.

and to handle errors, this will return "a" if the student is absent from all tests

Please Login or Register  to view this content.