Representing letters (and symbol) to different numbers
 

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 (please see the attachment for the explanations).
______________________________

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

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

Here's one way. See attached.
It relies on the table being sorted the other way round, so lowest points at the top and highest at the bottom.

It uses CHOOSE() to complete a "left lookup" and a "nearest match" to give the correct grade without having to have the exact score.

Let me know if that helps.

S.

Thank you for the reply.

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

-I write down 5 different letters (A to E) vertically downwards, one 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, the exact opposite of it, so I tried "reversing" the formula but to no avail. I'm wondering if it wasn't meant to be done in excel...

So each letter has a specific number it will ALWAYS return?

Representing letters (and symbol) to different numbers
 

"bunalz" wrote:
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,D3:D7 )/SUMPRODUCT(C3:C7,D3:D7)

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,D3:D7)/SUMPRODUCT(C3:C7,D3:D7)

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,D3:D7)

-----

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))

-----

For details, see https://www.box.com/s/ec52ae89a2652dcfea8c.

@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! Thank you!:D

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

Why the weight% didn't added up to 1 is because it wasn't really a distributed (ratio) weight. It is independent of each other. Each 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 of 40%)
LV4&5 modules are only available in the final year; that's why it's "heavier".
I can't explain very well. If you're curious you can read more he http://www.ubd.edu.bn/index.php?id=337

MC = no. hours of workload (which leads to having less learning content). Actually, the final grading does not just depend solely on the 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. Thanks Joeu! Thanks Spencer!

Representing letters (and symbol) to different numbers
 

FYI.... I wrote:
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,D3:D7 )/SUMPRODUCT(C3:C7,D3:D7)

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,D3 :D7)/SUMPRODUCT(C3:C7,D3:D7)

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,D3: D7)/SUMPRODUCT(C3:C7,D3:D7)

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.