Letter grades average

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!
 
R

Roger Govier

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top