How to find avg, min & max for grading subject

[Param]

Hi, with help from Mr.Ardus Peter, sjoblom & philips, I manage to get some
information about formula for grading. But I still having problem.

my grades all at cell e.g A2:A35. (beside I enter grade A,B,C,D,E there is
also
simbol "-" and "x".('-' for not taking the subject and 'x' for absent)

I try this:

Average: =CHAR(ROUND(AVERAGE(CODE(A2:A35)),0))
Max: =CHAR(MIN(CODE(A2:A35)))

work fine. But when i enter simbol "-" or "x" it show #value.

Pls help me how to overcome this problem. TQ

 

[Bernard Liengme]

It's ugly but seems to work
=CHAR(ROUND(SUMPRODUCT(--(CODE(A2:A35)>=65),--(CODE(A2:A35)<=69),CODE(A2:A35))/SUMPRODUCT(--(CODE(A2:A35)>=65),--(CODE(A2:A35)<=69)),0))

 

[Biff]

Hi!

I think you'd be better off just using a number grade.......or, using helper
cells to convert the letters to an equivalent number.

Entering those chars: "-", "x" in the range shouldn't cause an error but
they would be included in the calculation which will lead to incorrect
results. The only way I could generate an error is if there were empty cells
within the range. Is that a possibility?

To exclude those chars from the calcs:

Entered as an array:

If you only want to account for letter grades A, B, C, D, E:

=CHAR(ROUND(AVERAGE(IF((CODE(A2:A35)>=65)*(CODE(A2:A35)<=69),CODE(A2:A35))),0))

=CHAR(MIN(IF((CODE(A2:A35)>=65)*(CODE(A2:A35)<=69),CODE(A2:A35))))

Biff