how to equalize the value of an "alphabet" to "zero"

[kernelx]

I have a combine marks sheet of 10 students. some of the student was absent on paper day so I marked them "A" 9absent in the sheet. now I want to equalize the value of A=0 . as it has some impact on average value. i am posting two snapes to clear my point;









Here If I put the value "A" in "4F" average will be "51.13 % in "18F" but if I replace "A" by "0" then average will be "45.44" . I want to show "A" in cell but it value should be considered as "0" in average formula. kindly help...

[snb]

Why not posting an Excel file ? It's an Excelforum after all.

[mrmmickle1]

You may try using a mixture of these three functions: =SUM(F4:F13)-(SMALL(F4:F13,1))/(COUNT(F4:F13)-1)... I can't quite get the order of operations right but I believe this is what you want. It drops the lowest grade....

Or you could just put a zero and put a comment in that tells you the student was absent.

[alansidman]

Try using AverageIf function.

MS Excel: AVERAGEIF Function (WS)

[Admin]

Hi

Try

=AVERAGE(INDEX(SUBSTITUTE(F4:F13,"A",0)+0,0))