Representing letters (and symbol) to different numbers

[bunalz]

I'm an undergraduate student trying to create a formula so that wheneve
I put in my alphabet semester's exams grades, those letters will b
"converted" to as number values to make calculations possible (pleas
see the attachment for the explanations).
______________________________

I know I could directly input the grades as values but, rathe
frequently, me and my friends end up inputting wrong grade values (an
sometimes the whole calculations). I just wanted to easily input th
letters, and let Excel will calculate it for me.

I don't think "VLOOKUP" or "COUNTIF" will be of help since I will kee
dealing with different numbers everytime, though I could be well wrong
I'm no expert :

+-------------------------------------------------------------------
|Filename: Marking Formula.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=521
+-------------------------------------------------------------------

 

[bunalz]

Thank you for the reply.

I'm sorry if my explanation was a bit unclear. Here's the situatio
(Just the concept -- the whole thing isn't going to be sensible):

-I write down 5 different letters (A to E) vertically downwards, on
each line, on a blank paper.
-I give the paper to Ms. Excel and she would reply me with a number.

Sorry if the above is weird. The formula that you gave was, in fact, th
exact opposite of it, so I tried "reversing" the formula but to n
avail. I'm wondering if it wasn't meant to be done in excel..

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[joeu2004]

I'm an undergraduate student trying to create a formula
so that whenever I put in my alphabet semester's exams
grades, those letters will be "converted" to as number
values to make calculations possible [....]
|Download: http://www.excelbanter.com/attachment.php?attachmentid=521|

My comments refer to the Excel file example in the zip file, which differs
from your description somewhat. The Excel file calculates GPA for grades in
several courses, not of exam grades (weighted course grade). The mechanics
are the same in either case.

I do not agree with the way that you compute GPA. But we will deal with
that later.

Ostensibly, if your Grade Point table is in A11:B20, we would like to
compute the GPA (according to your dubious formula) with the following
array-entered formula (press ctrl+shift+Enter instead of just Enter):

=SUMPRODUCT(VLOOKUP(B3:B7,A11:B20,2,0),C3:C7,D37)/SUMPRODUCT(C3:C7,D37)

But that does really work(!). It produces a numerical result; but it is
wrong.

The crux of the problem is: VLOOKUP(B3:B7,...) effectively returns only
VLOOKUP(B3,...) instead of the intended array.

I cannot find a work-around that truly works. So I would create "helper
cells" in column E (labeled Grade Point, if you wish). Put the following
formula into E3 and copy down through E7:

=VLOOKUP(B3,$A$11:$B$20,2,0)

Then your (dubious) GPA formula becomes:

=SUMPRODUCT(E3:E7,C3:C7,D37)/SUMPRODUCT(C3:C7,D37)

That does produce about 3.46 when formatted to 2 decimal places.

However, I believe the correct GPA formula is:

=SUMPRODUCT(E3:E7,C3:C7)/SUM(C3:C7)

Alternatively, if these were exam grades, we would rely on the weights in
column D. However, percentage weights should sum to 1. Yours do not.

Assuming that the percentage weights are adjusted to sum to 1, the weighted
average is:

=SUMPRODUCT(E3:E7,D37)

-----

Finally, it might be nice to convert the computed GPA (or weighted course
grade) to a letter grade.

To that end, it would be nice if the Grade Point table were sorted in
ascending order by Grade Point; that is, F to A+ instead of A+ to F.

That would not change any of the formulas above, because we used an
exact-match look-up, to wit VLOOKUP(...,FALSE).

If the computed GPA (or weighted course grade) is in F3, the letter grade
can be determined as follows:

=INDEX($A$11:$A$20,MATCH(ROUND(F3,2),$B$11:$B$20))

Note that I round F3. This assumes that we display GPA to 2 decimal places,
but we did not explicitly round to 2 decimal places in the GPA calculations
about. Nonetheless, presumably we would want the letter grade to reflect
the __displayed__ (rounded) GPA.

Alternatively and preferrably, we could round the GPA calculation above.
For example:

=ROUND(SUMPRODUCT(E3:E7,C3:C7)/SUM(C3:C7),2)

Then the letter grade formula is simply:

=INDEX($A$11:$A$20,MATCH(F3,$B$11:$B$20))

 

[bunalz]

@Spencer: Yeah, kind of like that.

@joeu: That's exactly what I'm looking for! So in the end "VLOOKUP
really did what I thought wasn't possible. I learnt a lot today! Than
you!

--------------------------------
The formula is actually the one preferred by our university.
The reason behind including the "module's unit/credit" is because i
reflects the "weight" of the course (while weight% reflects th
difficulty/depth of the course).

Why the weight% didn't added up to 1 is because it wasn't really
distributed (ratio) weight. It is independent of each other. Eac
module/course is assigned to a code e.g: XY-1201. X, Y = Faculty
Programme. 1st digit = Level of module: LV 1, 2, 3, 4, 5

LV 1-3 carry level weightage of 20%; LV 4 & 5 carry level weightage o
40%)
LV4&5 modules are only available in the final year; that's why it'
"heavier".
I can't explain very well. If you're curious you can read more h
http://www.ubd.edu.bn/index.php?id=337

MC = no. hours of workload (which leads to having less learnin
content). Actually, the final grading does not just depend solely on th
exams results but some other factors such as assignments, tests
presentations, reports, and even things like academic attitude.
---------------------------------

Anyway, I'm really thankful for all those incredible efforts. Thank
Joeu! Thanks Spencer

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[joeu2004]

Ostensibly, if your Grade Point table is in A11:B20, we would like to
compute the GPA (according to your dubious formula) with the following
array-entered formula (press ctrl+shift+Enter instead of just Enter):

=SUMPRODUCT(VLOOKUP(B3:B7,A11:B20,2,0),C3:C7,D37)/SUMPRODUCT(C3:C7,D37)

But that does really work(!). It produces a numerical result; but it is
wrong.

The crux of the problem is: VLOOKUP(B3:B7,...) effectively returns only
VLOOKUP(B3,...) instead of the intended array.

Apparently, I discovered that at least a year ago. The upshot of the
discussion is: there is no "good reason" why VLOOKUP(B3:B7,...) does not
work as intended when array-entered. It's just one of many "little
inconsistencies" in Excel.

(The operative words are "as intended". As noted previously, VLOOKUP does
return a value; just not the intended value.)

A couple work-arounds were mentioned. The most straight-forward is the
following array-entered formula (press ctrl+shift+Enter instead of just
Enter):

=SUMPRODUCT(LOOKUP(B3:B7,A11:A20,B11:B20),C3:C7,D37)/SUMPRODUCT(C3:C7,D37)

That assumes that A11:A20 is sorted alphabetically (A,A+,B,B+,C,C+,D,D+,F,P)
with corresponding grade point values in B11:B20.

The following array-entered formula (press ctrl+shift+Enter) is more arcane.
But it allows us to use the same table to do a reverse look-up in order to
convert the computed GPA into a letter grade.

=SUMPRODUCT(SUMIF(A11:A20,B3:B7,B11:B20),C3:C7,D37)/SUMPRODUCT(C3:C7,D37)

That assumes that B11:B20 is sorted in ascending order
(0,1,1.5,2,2.5,3,3.5,4,4.5,5) with corresponding letter grades in A11:A20
(F,P,D,D+,C,C+,B,B+,A,A+).

Note that SUMIF is a bit misleading in this context: it functions as a
lookup, and it produces just one value, not a sum of values, since there is
only one match.