M
Mathsteach
How do I use Excel to average students grades given in letters?
R
Ron Coderre
Here are some ARRAY FORMULA* ideas....
*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].
For student grades (A,AB,B,....F) in B2
2
Example:
B2: A
C2: B
D2: A
E2:
=INDEX({"A","AB","B","BC","C","CD","D","F"},ROUND(AVERAGE(LOOKUP(B22&"",{"","A","AB","B","BC","C","CD","D","F"},{FALSE,1,2,3,4,5,6,7,8})),0))
That formula returns: AB
Or
E2:
=AVERAGE(LOOKUP(B22&"",{"","A","AB","B","BC","C","CD","D","F"},{FALSE,1,2,3,4,5,6,7,8}))
That formula returns 2.33
Something you can work with?
***********
Regards,
Ron
XL2002, WinXP
*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].
For student grades (A,AB,B,....F) in B2
2Example:
B2: A
C2: B
D2: A
E2:
=INDEX({"A","AB","B","BC","C","CD","D","F"},ROUND(AVERAGE(LOOKUP(B2
2&"",{"","A","AB","B","BC","C","CD","D","F"},{FALSE,1,2,3,4,5,6,7,8})),0))That formula returns: AB
Or
E2:
=AVERAGE(LOOKUP(B2
2&"",{"","A","AB","B","BC","C","CD","D","F"},{FALSE,1,2,3,4,5,6,7,8}))That formula returns 2.33
Something you can work with?
***********
Regards,
Ron
XL2002, WinXP
B
Biff
Just for the heck of it........
Grades are: A, B, C, D, F
Array entered: (doesn't account for empty cells or any other entries that
are not letter grades)
=CHAR(ROUND(AVERAGE(CODE(UPPER(A1:A5))),0)+(ROUND(AVERAGE(CODE(UPPER(A1:A5))),0)=69))
Biff
Grades are: A, B, C, D, F
Array entered: (doesn't account for empty cells or any other entries that
are not letter grades)
=CHAR(ROUND(AVERAGE(CODE(UPPER(A1:A5))),0)+(ROUND(AVERAGE(CODE(UPPER(A1:A5))),0)=69))
Biff