Turning Grade % into Letter Grades

[celticmoose]

I need helping creating a function to turn a percent (0-100) into a letter grade, I am creating a gradebook spreadsheet.

I think VLOOKUP is the way to go here, but I do not understand vlookup at all no matter how many times I try to look at demos or read how-tos.

I created two columns, one with what the grade entails (93-100 , 90-93 and so on) and the next column has A, A-, etc. But I can't get vlookup to work, any suggestions? I am pretty noob when it comes to excel.

 

[stanleydgromjr]

celticmoose,

Try this:
=LOOKUP(A1,{0,65,70,75,80,85,90,94},{"F","D","C-","C+","B-","B+","A-","A+"})

Have a great day,
Stan

 

[gallan]

I am a teacher and have written my own very extensive grade book.

Why not use a select case statement?

select case gradelevel
case is >= 93
LetterGrade = "A+"
case is >= 90
LetterGrade = "A"

etc

or a set of elseifs

if gradelevel >= 93 then
LetterGrade = "A+"
elseif gradelevel >= 90 then
LetterGrade = "A"

etc

although maybe the VLookUp is a good idea; I have just not bothered to investigate it. But I would prefer to have a table on some sheet that I could easily change if I want rather than in the VBA code itself assuming that is what you will use

 

[Gates Is Antichrist]

Gary those are fine. Reasons for leaning to vlookup are chiefly stylistic. The short vlookup is more concise. You can set one centralized location for "control data" (the vlookup rectangle) vs. "processing data" (where you do the calculations). For maintenance, such as adding a letter or changing the values, you simply modify the control table instead of having to analyze [sometimes long] formulas. You change the table once, vs. having to copy the formula changes (which may not be conveniently contiguous in some applications).

In some applications it's easier to deal with vlookup(...) giving an error due to failure than having to deal with 3 or 10 or whatever number of comparisons.

The biggest "gotcha" in using vlookup, or moreover with anything that references a range of data (whether it's $a$2:$B$6 or GradeTable), is when you append to the range. In that case you must ensure that the range still covers the expansion. Often I insert a row or column in a range in that case, because Excel automatically adjusts the range when you do that.

Another aspect is calculation speed. When you deal with thousands of vlookups in a workbook it can take a while to calculate - sometimes painfully. In that case experimentation with alternatives is very worthwhile. Lots of struggle and discussion has revolved around the slowness of lookups when there are many, with one popular solution being to use MATCH and OFFSET.

Finally, your solutions involve using code(VBA). You'll notice that many replies in this forum immediately pounce to a VBA solution to a question. There's another school of thought that VBA should be avoided if a non-VBA solution is available; I'm generally in that camp. There's no hard and fast rule but I give weight to several considerations:
- Does the application person even know VBA? Many have no idea and perhaps are as-yet intimidated, particularly beginners. Does celticmoose?
- Does speed matter? (Sometimes VBA is faster, though often slower.)
- Must others maintain it? If so, it points strongly - not absolutely - to avoiding VBA.
- Is the workbook distributed to a general population? Some may be in an environment that restricts running macros. If instead there's that macro prompt when opening the workbook, well, when YOU receive a spreadsheet, do you want to go through the deliberation of whether to answer "yes" to that question of whether to allow macros, not knowing what it does or spawns, vs. code where you can "see everything?" If the question can be eliminated by keeping all functionality in the sheet with no code, no worries. (For example, when I prepare a workbook for the CEO, I don't want to make him answer that prompt, if there's no or low cost to avoid it.)

Just some thoughts.