"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.
Office