not counting absent students marks

[Tim K]

Hi,


I just joined so apologies if I inadvertently missed a few posting conventions.

I was wondering how to not count a student's marks if they are absent or have left and also have the spreadsheet not count the incorrect total marks to get the percentage. So the result below should be Student A 14/28=50%, Student B 43/43=100%, Student C 4/12=33.3%. (also, not necessary but handy would be for the spreadsheet to also copy the word "left" to designated cells ie if you type it in C5 it is programmed the text "left" appears in D5 as well).


eg A B C D

1 Out of 12 15 16

2

3 Student 1 6 abs 8

4 Student 2 12 15 16

5 Student 3 4 left left


Thanks for your help in anticipation,


Tim K

 

[kchiba]

Try this formula, it assumes that the "Out of" is in B1:D1, student names are in A2:A4,

marks and comments are in B2:D4.

Put this formula in F@ and copy down


=SUM(B2:D2)/SUMIF(B2:D2,"<>*",$B$1:$D$1)

 

[Tim K]

kchiba,


Thanks for that elegant solution. It is much appreciated. Is the <>* meaning does not equal text?


Cheers,


Tim K

 

[kchiba]

Hi Tim


That is correct <>* is not equal to text.


Kanti