How to find average for grading?

[Guest]

I have grades that (link from diferrent worksheets)
e.g.

A1: C
A2: A
A3: D
A4: #DIV/0! (because no input at that particular worksheet)
A5: #DIV/0! (because no input at that particular worksheet)

Formula I used as below:

ARRAY FORMULA*
A6: =AVERAGE(IF(A1:A5>0,A1:A5))

the return answer is #div/0!

how to modify this formula to cater for grades.

 

[T. Valko]

Do you want the average *letter* grade? What are the possible letter grades?
What average would you expect from C, A, D ? What average would you expect
from A, B ?

Biff

 

[Guest]

Somewher you would have to assign numeric values to the grades in a table,
for example

cole Colf
A 10
B 9 etc

=AVERAGE(VLOOKUP(A1,E18:F19,2,FALSE),VLOOKUP(A2,E18:F19,2,FALSE))

With the grades in column A, a formula like the above would check for the
numeric value for each grade letter and return the average.

On the other hand if you simply want to average the characters and return a
character then try


=CHAR(AVERAGE(CODE(A19),CODE(A20)))

the would return the average of 2 grades in A19 & a20 but note that the
average of a and A gives a different answer to the average of A and A.


Mike


and then look

 

[Bob Phillips]

Try this

=LOOKUP(AVERAGE(IF(NOT(ISERROR(A1:A5)),LOOKUP(A1:A5,{"A","B","C","D","E"},{1,2,3,4,5}))),{1,2,3,4,5},{"A","B","C","D","E"})

It may or may not be right, depending upon the formula that generates the
original grade, it would be better to go back to that formula.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)