# Turning Grade % Into Letter Grades

Aug 11, 2007
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.

View 9 Replies
ADVERTISEMENT
Mar 30, 2008

I have a range of cells C11:C29,G12:G18,G20:G23,G25:G26,G28:G29,C33:C42,G33:G42,C46:C47,G46:G47,C51:C54,G51:G59,C58:C59 that contains letter grades (egs. A+,C- etc). I need the code for a macro that checks each cell from this range and if the value of the cell is A+ then the cell's value changes to 100, if the cell's value is A then change the cell's value to 98, if cell's value is B+ then change to 88 ...and so on...you get the idea (i will complete the rest of conditions but i just need the basic code).

View 6 Replies
View Related
Jan 23, 2014

I am trying to average letter grades in a spresdsheet my formula seems to work, but if any of the columns in the spreadsheet are totally blank I get #DIV/0! in the cell where I should see the average grade. Is there anyway to stop this from happening. This is the formula I am using.

=CHAR(INT(SUM(IF(E21:E150<>"",CODE(E21:E150))/COUNTA(E21:E150)))) assay entered.

View 5 Replies
View Related
Jun 21, 2007

a formula that would take letter grades (A, B, C, D, and E) and produce an average letter grade?

For example, a student gets a D on one assignment and a B on another. I want a formula that will produce "C".

View 3 Replies
View Related
Oct 9, 2008

Newbie here. I have a very frustrating problem. I am using excel for my gradebook at school. I've tried several different ways to assign a letter grade to an number average. It works fine each different way I do it, VLOOKUP, IF, etc. But, I have about 5 grades out of 100 which give the wrong letter grade. For instance, my scale says that a grade of 85 should be lowest limit of a "B", but I get a "C" returned in the cell instead. Like I said, it only happens on a few grades. The biggest majority work fine. I can't figure out why. Any ideas? Attached is one of the "problems" with a student's grades. Note the Final Avg with a grade of "C", it should be "B".

View 12 Replies
View Related
Feb 14, 2009

I, too, am trying to use excel to the fullest. My first issue is, I would like to convert the numberic grade in one cell to a letter grade in another cell. I am not really good with all the vocabulary, but would love to learn it, and can copy a formula pretty well!!!! Not so good with functions and macros but would love to learn. All help appreciated.

View 11 Replies
View Related
Dec 29, 2013

I created an Excel 2010 macro enabled workbook (.xlsm) which calculates the letter grades of test scores between 1 and 100.

To use the workbook:

1. Put the scores in one column of the open worksheet.

2. Select the first score with the mouse (i.e. make this the "active cell").

3.Click on the "Calculate Grades" button on the sheet.

Public Function LGrade(ByVal score As Variant) As String

'Returns the letter grade based on a test score.

On Error GoTo ErrHandler

Select Case score

Case Is > 100: LGrade = "A+"

Case 95 To 100: LGrade = "A"

Case 90 To 94: LGrade = "A-"

Case 87 To 89: LGrade = "B+"

[code].....

View 4 Replies
View Related
May 9, 2006

I have figured out how to assign a letter grade to a number, but am having trouble assigning it the other way, a number to a letter grade. For instance: If a student gets an A, I want the column next to it to indicate that the A represents a 4; a B represents a 3; a C represents a 2; D a 1; and F a 0. This will allow an easy grade point average calculation.

A 4 History

C 2 Math

A 4 English

B 3 Physical Ed

D 1 Science

GPA 2.80

View 3 Replies
View Related
Nov 28, 2012

Listed below are the grades for a student. Some columns have blanks. I have to find the highest grade (A being the highest) in the range.

I'm going up with the opposite (F) with this formula:

=CHAR(MAX(INDEX(CODE(A22:M22&CHAR(1)),0)))

How do I get A?

I'm using Excel 2007

A

B

C

B

D

E

A

A

B

F

View 6 Replies
View Related
Oct 3, 2006

Need to write a formula that would assign a letter grade (in row W) to the average in row V, based on the school's scale. So, row V contains my averages (in % form) and I want row W to have the letter equivalents.

93+=A

85-92=B

75-84=C

67-74=D

<66=F

View 2 Replies
View Related
Jul 10, 2013

I am compiling a master spreadsheet to analyse progress of students. I have their potential grades in one column and their latest progress grade in another.

I would like an automatic look up of the potential to the progress grade so that if their current grade is BELOW their potential that gets highlighted one colour, if it is above, another colour.

One issue is that the grades are all letters (S, A, B, C, etc.) and that some potential grades are 'dual' (i.e. A/B, S/A) which complicates the issue somewhat.

View 7 Replies
View Related
Jun 13, 2006

my Excel teacher doesn't even know how to do this! The chapter is Working with Multiple Worksheets and Workbooks, and we went into linking workbooks, linking worksheets, using the VLOOKUP function, and saving workbooks as templates. This is Case Problem 2 and i'll attaching the file.

It says:

1. On the Grading Criteria worksheet, enter the text "Exam Average" in cell A1

and the text "Grade" in cell B1.

2. In the range A2:B6, create a lookup table for the range of grades specified by Professor Templeton. (Hint: Each letter grade should be matched up with the lowest score possible for that grade.)

Here are the specified grades:

0 to 49 = F

50 to 59 = D

60 to 74 = C

75 to 89 = B

90 to 100 = A

3. Insert a formula in Column E of each worksheet to calculate the overall score of each student based on the grade table you entered in the Grading Criteria worksheet.

View 9 Replies
View Related
Mar 2, 2009

When I type a single lower case letter into a cell, what formula or conditional formatting should I use to always convert it to a capital letter automatically?

View 9 Replies
View Related
Dec 31, 2009

For the below formula is it possible to replace the B's (column location) with a cell Say Z146 which contains the letter B (or a number if thats easier and someone can tell me the numbers for each column).

When the formula is dragged into the next cell (down) it takes its column reference from Z147 and then my life becomes so much easier.

=IF(INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0))"",INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0)),"")

View 9 Replies
View Related
Mar 7, 2014

I need to change a few hundred cells (one column) where the first letter may be a capital letter to a lowercase letter.

So:

GetAwardfromBid to getAwardFromBid

or

SmallLertter to smallLetter

View 3 Replies
View Related
Jun 24, 2014

I have a work problem that I'm not sure which functions to use. I'm creating a grade calculator...here is the problem:

I have 18 total lessons (cells A1:A18). Each lesson is worth either 3 points or 0 points. Of the 18 lessons, only 10 count towards my grade. What functions do I use to count only 10? the countif function? Do I use a nested IF?

remember that of the 18 lessons, only 10 (any 10, not just the first 10) count.

View 2 Replies
View Related
Aug 15, 2007

I've got all the grades sorted out for the Principal, she is now looking for me to tally up all the students who got straight 'A' grades. I know there is going to be anotehr combination here but I'll start with the basics of straight 'A's

The screen below shows how the data is presented although I've slimmed down the headings to fit better.

How do I insert a formula to look at each row to see where based on the subjects taken, there are straight 'A's

************************************************************************>Microsoft Excel - Y13 & 14 Combined Worksheet.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutZ6=

ABCDEFGHIJKLMNOPQRSTUVWXYZ1ArtArt*2BiologyBSChemistryD&TDramaEconomicsE.*LitFrenchGeographyHistoryH.*EconICTICT2IrishLatinMathematicsMaths*PureMusicPhysicsPoliticsPsychologyR.ESociologySpanish2********A****A**********A*3********AA*A*******A******4******A*C*****D****B******5***********B**********BAA*6*******C***D************BBSheet1*

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies
View Related
Sep 16, 2009

I have a list of students who achieve grades A-G in subjects and I have organised these into columns. I then need to be able to be able to count a total of how many grades they have achieved, how many As, Bs etc. So far I have managed this but...I need to be able to attach different values to the grades. Some subjects are worth 0.5, some 1 and some 2. I cannot work out how to count if they achieve a grade A worth 0.5 + a grade B worth 1 etc.

View 9 Replies
View Related
Oct 6, 2009

I have few grades and conditions on which rate will populate in the D1 cell.

Tried at bit

=IF(OR(A1="x10",A1="x20"),"150",IF(A1="x30","240",IF(A1="x40","190",IF(A1="x60","340","50"))))

GradeConditionsRates

x10offshore150

x10Onsite320

x20offshore150

x20High Skill170

x20Onsite320

View 9 Replies
View Related
Apr 2, 2009

I have an excel spreadsheet with my students' grades on it. I want to be able to click on a student's name and have a list of all the assignments, along with only that student's corresponding grades, transposed on a new sheet so that I can print out to give to a parent.

View 13 Replies
View Related
Nov 25, 2013

I am trying to figure out a simple formula on determining class for students according to their achievement /grades. There are 9 classes altogether ( 7A, 7B, 7C, 7D, 7E, 7F, 7G, 7H and 7J). There are three columns for subject ( MATHS, ENGLISH, SCIENCE). So lets say the student get Grade A, A , A : The student will belong to Class 7A.

As attached.. TEST PSR.xls

View 2 Replies
View Related
Apr 16, 2013

I am attempting to create an IF or IF(AND formula that I can use to display the letter grade I earned based off of a certain percentage criteria.

"A" D11>=93%

"A-" D11>= 90%, D11=87%, D11=83%, D11=80%, D11=77%, D11=73%, D11=70%, D11=67%, D11=63%, D11=60%, D11

View 7 Replies
View Related
Sep 23, 2013

I need to calculate the average of 5 grades from A,B,C,D, and E. However I want it to the average even if any number of grades (i.e. up to 5) are entered.

e.g

grade1 grade 2 grade 3 grade 4 grade 5

A A C B E

I have assigned a vlookup table to convert to numerical values, and got it to calculate the average, but it falls apart when any grade is missing.

I have used the iferror command on the different calculations, but when i put them to gather into one large string, again it falls apart.

View 8 Replies
View Related
Dec 7, 2008

I am having trouble coming up with a formula to calculate the weighted average of students Finals grades. This is the format my sheet is in to give u an idea.

-----------------------------------------------------------------------

Test # 1 Test # 2Test #3Assignment #1Assignment #2Final Test Final MarkPossible Points:1001001001010100Percent Worth:20%20%20%5%5%30%Students:Anderson, Ken6473788768 ?Carter, Bill72768473.587 ?Dubeers, Ashley84887610590Jackson, Kevin7666549873Parker, Jerome82657679.560Remini, Leah969290101089Thompson, Chuck5042806.5760Vaughn, Sarah7265658970

----------------------------------------------------------------------- -

I have to come up with students averages based on the mark, the percent each grade is worth and the maximum amount of points its worth. Anyone know the formula or how to calculate the final grade using all of these factors?

View 9 Replies
View Related
Jan 4, 2008

I am in need of some more assistance regarding finding out a formula to find out the average grade of a pupil which ranges from letters 'A to E. This maybe be a simple task for experts, but unfortunatley spreadsheets are not my strong point. D7:D14 is the coloum that i wish to have an average of the grades acheived by each individual pupil in thier correspongin rows (on the right hand side of D7:D14) http://i41.photobucket.com/albums/e2...2/example2.jpg

View 5 Replies
View Related
Jun 3, 2009

Need to calculate different percentiles based on different grades. how do I put an 'if' loop or anything else that will work for this.

GradeAmount

B23

B22

B35

B36

B48

B49

B510

B511

B615

B23

B37

B614

Need function for the table below

Percentile10%25%50%60%75%90%

B2

B3

B4

B5

View 12 Replies
View Related
Jul 17, 2006

I have created a gradebook and it has several types of workbooks for the student grades. 30% of the grades are calculated in different workbooks. The main gradebook is where the teacher places the mid term, final term grades. In the same workbook the teacher must place the grades for the following areas. (Participation Grade, Following Rules, Homework Grade, and Monthly Test). Now this is my problem!!! In the main grade workbook I need to automatically place the grades from the other workbooks.

Example: ='Participation English P-6'! ( Cells - AN84 for July, AN160 for September, AN274 for January, and AN350 for MArch. I tried to use = COUNTIF(AN84:AN160,AN274,AN350) but it will not work. I can get one grade at a time by using ='Participation English P-6'!AN84 , which will give me the grade in the main workbook that I need. But I don't want to change everytime the grade period comes around. forumal that will give me grades from the other workbooks at the grade time from these cells?

View 9 Replies
View Related
Mar 19, 2013

I am trying to calculate final grades with weighted averages. However for the final grade I need to drop the lowest of the test grade which come after the bold line to the right, making the total relative weight of Tests, the weight of 2 tests. How would I go about calculating that with a SumProduct formula? I do not need to drop grades from B4:K4, however from L4:N4 I need to drop the lowest grade for my final grade, which needs to be rounded to the nearest integer. The relative weight of each assignment is given in Row 1.

View 3 Replies
View Related
Mar 30, 2009

Using Excel 2003,

I'm trying to come up with a formula that will take the students Total Pct. (column G, G12 is first cell & G41 is the last) from the worksheet called Juniors and find their Total grade (Column H, H12 is first & H41 is last) from the worksheet called Total Days. The % column on the Total Days worksheet is A (cells A10:A22) and the Grade column is B (cells B10:B22)

Juniors worksheet

Tot. Pct.

Tot.Grade

119.5%68.3%90.2%80.6%78.0%70.7%95.1%165.2%95.1%200.0%

Total Days worksheet:

%Grade99A+93A91A-89B+83B81B-79C+73C71C-69D+63D61D-60F

View 9 Replies
View Related
Oct 13, 2013

I was planning to use some combination of INDEX,MATCH and/or the SUMPRODUCT function to complete this task.

In the past I've used the SUMPRODUCT function creatively { ex. =SUMPRODUCT(--(repname=$C$2),--(question=C5),--(test=$E$3)) }

However, since the criteria I need to consider are not organized in individual records, the SUMPRODUCT function did not work.

See the attached work book for details on the test grade data.

Test Data Workbook.xlsx

The workbook also includes a mock-up of the summary and detail I'm trying to create.

My specific questions are on the "Questions" tab.

View 1 Replies
View Related