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.

 

[paul_bam]

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.

Hello,

I think you would be better off using an if statement. Lets say for example you have all your percentages in column A and you wish to display the grades in column B. Lets also say that your grades start at cell A2.

The if statement would be something like this

If(a2>93,"Grade A","")

And so on, you will have to nest the rest of the if statement to calculate the rest the grades.

Hope that this helps

Paul

 

[stanleydgromjr]

celticmoose,

Welcome to the board.

You post is not clear - conflicting grade numbers:

grade entails (93-100 , 90-93 and so on) and the next column has A, A-,

What are the ranges for each grade?
A, 94-100
A-, 90-93

If the first grade number is in cell A1, try this for a start in cell B1:
=IF(A1>93,"A",IF(A1>89,"A-",IF(A1>85,"B",IF(A1>79,"B-",IF(A1>74,"C",IF(A1>69,"C-",IF(A1>64,"D","F")))))))

Have a great day,
Stan

 

[Gates Is Antichrist]

celticmoose, welcome to the board. You asked a good question and expressed it well.

vlookup looks for an exact match in the lookup table if you use the fourth argument as ,FALSE . If you say ,TRUE for the fourth argument (which is the default if you omit the arg), you must sort the first column of the lookup table. And make it ascending instead. So in column A enter
0
70
80
90
93
and put the grades in column B.

=VLOOKUP(85,$a$2:$B$6,2,true)

I further encourage you to name the range as a table so that you don't have to worry about $. Then you'd go
=VLOOKUP(85,GradeTable,2,TRUE)

And lighten up! A 90 is an A in my universe! Unless you're a Microsoft employee grading interfaces, in which case 100 is an F and 15 or under is an A!

 

[celticmoose]

You can have only 7 nested IF statements, correct?

And I was curious about the grades myself, I just was copying the instructions from the teacher. It doesn't make sense to have a 93 be an A and an A-. I'll worry about that later, though, I can easily change that.

The teacher recommended that I use vlookup, my worst enemy.

I just don't know what to enter in the formula

=vlookup(?,mycolumns,?,?)

This is what I have =VLOOKUP("A",Z59:AA70,1,AH) I have no idea how to set a vlookup up.

 

[Gates Is Antichrist]

Keep at it with vlookup, because it's really a great fit for this quest. If you use IF statements you start nesting IFs and IMO the formula gets unwieldy, vs. the concise form of this vlookup.

 

[Gates Is Antichrist]

And I just realized you showed "A-" signifying that 93 is an A and 90 is an A. Now that I've realized my oversight, disregard the "lighten up" part

 

[celticmoose]

Okay so I got it working with several grades, but a majority of them give me the #N/A bit.

UPDATE: Success! Thanks a bunch!

 

[celticmoose]

Okay new problem... sorry again, how do I count the # of As, A-s, etc?

Nevermind, COUNTIF.

 

[Gates Is Antichrist]

Right. You might also have some statistical interest in AVERAGE, MAX, MIN, and RANK if you want to go further with this grades project.