Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 :( |
#2
|
|||
|
|||
Quote:
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. |
#3
|
|||
|
|||
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... |
#4
|
|||
|
|||
Quote:
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
|
|||
|
|||
@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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Formating Letters to Numbers while Displaying Letters | Excel Worksheet Functions | |||
Representing numbers with K, M, G sufix using a macro | Excel Programming | |||
Can I have words representing numbers in a formula? | Excel Discussion (Misc queries) | |||
how to convert whole numbers representing seconds to mm:ss | Excel Worksheet Functions | |||
FORMULAS FOR ADDING LETTERS REPRESENTING NUMBERS | Excel Discussion (Misc queries) |