#### celticmoose

##### New Member
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.

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### paul_bam

##### New Member
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

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

##### Banned
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

##### Well-known Member
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

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

##### New Member
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

##### Well-known Member
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

##### Well-known Member
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

##### New Member
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

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

Nevermind, COUNTIF.

#### Gates Is Antichrist

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

Replies
0
Views
124
Replies
2
Views
340
Replies
12
Views
631
Replies
0
Views
208
Replies
9
Views
598

1,190,782
Messages
5,982,878
Members
439,803
Latest member
sushilneupane

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back