Average for Grade

[Guest]

Hi, I use below formula for marks(numbers)

For values in A1:A100

B1: =SUMIF(A1:A100,">0",A1:A100)/COUNTIF(A1:A100,">0")

OR

ARRAY FORMULA*
B1: =AVERAGE(IF(A1:A100>0,A1:A100))

My Question: How to modify this formula for grade?

 

[Bob Phillips]

Maybe something like

=LOOKUP(AVERAGE(IF(A1:A100>0,A1:A100)),{0,50,60,70,80,90},{"F","E","D","C","B","A"})

as an array formula

HTH

Bob

 

[Guest]

Hi,

How about this:

from cell range A1:A5 contain grades (link from another sheet). example:
B
C
D
DIV/0
DIV/0

Question: because there is div/0, my average formula doesnt work. how to
solve it?
TQ

 

[Bob Phillips]

What is your average formula?

--
HTH

Bob

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

 

[Guest]

=average(A1:A5)
*actually I really dont know how to create formula for this question.
TQ

 

[Bob Phillips]

No, I meant what formula returns A, B, C etc and the Div/0


--
HTH

Bob

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

 

[Guest]

below are the links to the diferrent file.

A1='C:\EMS\[Class1.xls]S5'!$AF$78
A2='C:\EMS\[Class2.xls]S5'!$AF$78
A3='C:\EMS\[Class3.xls]S5'!$AF$78
A4='C:\EMS\[Class4.xls]S5'!$AF$78
A5='C:\EMS\[Class5.xls]S5'!$AF$78

ps- at cell AF$78 (there are another formula link to another sheet to get
the answer) e.g
AF78=Sheet1!$V$37