Letter grades average

  • Thread starter Thread starter Antonio
  • Start date Start date
A

Antonio

Can anyone suggest a formula that would take letter grades (A, B, C, D, and
E) and produce an average letter grade based on different components?

For example, a student gets a D on one assignment that is worth 10 % of the
total grade, a B on another assigmetn that is 40 % and a A in an exam that
is 50 % of the total grade. What formual will produce the total grade?

Thanks!
 
Hi Antonio

One method.
as the Ascii code for A is 65, B 66 etc., you could take 64 away from each
grade value so
A 1
B 2
C 3
D 4
E 5

then use
=CHAR(ROUNDUP(1*0.5+2*0.4+4*0.1,0)+64)
which would result in B
as the result of the calculation would most often me a non Integer value, I
have used Roundup, before adding back the 64 as this would tend to lower the
overall grade rather than taking Int() of the value.

You could create a lookup table for the values and percentages using a list
like above with a third column showing what percentage is to be assigned to
each. Name this 5 x 3 table as Scores.
Then with Grades assigned in A1, A2 and A3 use

=CHAR(ROUNDUP(
VLOOKUP(A1,scores,2,0)*VLOOKUP(A1,scores,3,0)+
VLOOKUP(A2,scores,2,0)*VLOOKUP(A2,scores,3,0)+
VLOOKUP(A3,scores,2,0)*VLOOKUP(A3,scores,3,0),0)+64)
 
Back
Top