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
When speaking with parents I want them to enter in their child's birthday i.e. 12/1/1998 in one cell and then automatically generate what grade they should be in the next cell. If the grade year cutoff date is Sept 1.
View 2 Replies View RelatedNewbie 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 RelatedI 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
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 RelatedI have a very basic Excel Gradesheet that's designed to simply record letter grades, and show the grade average as the year progresses. I've hit a snag with problem in the formula which shows a grade of "F" - when the grade range can only be that shown on the table (A+ to E-). I've attached the Excel sheet so you can see what I mean.
View 8 Replies View RelatedI'm trying to determine the average of a set of numbers, based on a scale of 1 through 10. There are two columns and it would look something like this:
1 3,452
2 2,990
3 564
4
etc.
I want to determine the STDEV here as well but assume I can just exchange the AVG function with that function, once I know how to tie column B (the numbers) to column A (the rank).
I have a routine that looks a variable's first letter and then assigns it to a specific folder. I have tried using just the first letter (firstletter) and the ASCII value of the first letter but neither seem to work - see below.
firstletter = Left(rng.Value, 1)
iletter = Asc(firstletter)
MsgBox iletter
If iletter < 71 Then cmfolder = "CM A-F" Else
If iletter > 70 And iletter < 77 Then cmfolder = "CM G - L" Else
If iletter > 76 And iletter < 83 Then cmfolder = "CM M - R" Else cmfolder = "CM S - Z"
MsgBox cmfolder
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.
I have some delivery ratings from suppliers, and I want to assign a specific letter to each rating, depending of the obtained number. Criteria is:.........
View 4 Replies View Relatedcreating 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.
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 RelatedI 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].....
I ma going to have a couple of tricky (to me at least) table/range look ups, so let me start with this one:
.......a........b...................c.....................d....................e
1............Dive 1......................................School Grand Totals
2..team.....diver nr......... points...............Boys...............girls.
3 md b.......1..................3.....................md b...8..........md g...4
4 md b.......2..................3.....................jb b....7
3 jb b........3..................3................
6 md g,......4..................3
7
8..............Dive 2
9..team.....diver nr......... points...
10 md b.....1..................1...........
11 md b......2.................1...........
12 jb b......3.................4
13 md g......4................1
Notes about above. The naming is like this. "md b" is "martin county boys". "md g" would be "martin county girls". "jb b" is "jensen beach boys".
I need to add up each school grand totals for all the dives, and break them down to boy or girl for each school. this score will eventually go into another total which totals 11 swimming events by boys or girls.
So, in essense, thre are going to be several totals for the meet:
1 Diving (6 dives) by School by Boy (note: only showing 2 dives here)
2 Diving (6 dives) by School by Girl
3 Swimming, (11 events) by School by Girls
4 Swimming, (11 events) by School by Girls
5 Grand total by school (boys and girls scores combined)
Texaslynn (I believe that was her handle) helped me previously with totaling a grand total. However, now I discovered I need the total by boys and girls and then the combined (boys and girls) school total.
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
I am attempting to calculate Grade point averages for my students for all classes. There are 5 columns of grades to be considered. I frist assign values of 0-5 to the grades then average the points. My problem is that I want the AVERAGE to ignore the zero but it calculates it as well. (I enter a 0 if I have no grade for that class.) I have tried the{ } to make it an array formula but this also did not work. Here is what I have, can anyone help?
=SUM(AVERAGE(IF(AND(L2>89,L2<100),5,IF(AND(L2<90,L2>79),4,IF(AND(L2>69,L2<80),3,IF(AND(L2<70,L2>59), 2,IF(AND(L2>0,L2<60),1,0))))),IF(AND(T2>89,T2<100),5,IF(AND(T2<90,T2>79),4,IF(AND(T2>69,T2<80),3,IF( AND(T2<70,T2>59),2,IF(AND(T2>0,T2<60),1,0))))),IF(AND(AB2>89,AB2<100),5,IF(AND(AB2<90,AB2>79),4,IF(A ND(AB2>69,AB2<80),3,IF(AND(AB2<70,AB2>59),2,IF(AND(AB2>0,AB2<60),1,0))))),IF(AND(AJ2>89,AJ2<100),5,I F(AND(AJ2<90,AJ2>79),4,IF(AND(AJ2>69,AJ2<80),3,IF(AND(AJ2<70,AJ2>59),2,IF(AND(AJ2>0,AJ2<60),1,0))))) ,IF(AND(AR2>89,AR2<100),5,IF(AND(AR2<90,AR2>79),4,IF(AND(AR2>69,AR2<80),3,IF(AND(AR2<70,AR2>59),2,IF (AND(AR2>0,AR2<60),1,0)))))),-1)
I know it is huge. The syntax is correct. It calculates the average, but always for all 5 columns. It will not ignore a 0 in a column.
I'm doing a course at the mo and get graded monthly. I have a spreadsheet with my grades on 'assignment1, grade A, assignment2, grade B and so on.
What i want to do is have a cell that has the aveage of all grades. So if i had...
Grade A
Grade B
Grade C
the average is B.
I have the following grade scale
F = 0%, D = 60%, D+ = 65%, C = 70%, C+ = 75%, B = 80%, B+ = 85%, A = 90%, A+ = 95%
and I have these students total marks
84%
70%
72%
78%
81%
71%
76%
72%
74%
77%
72%
I want the student who took 84% which is the best to take A+
On other way, I want these marks to be on average instead of the standard scale
I have a formula that counts all instances of the letters a, b and c in a range and assigns them a value of 1 unless they are in the M column in which case a, b or c counts 4.
=SUM(COUNTIF(J2:AB2,{"a*","b","c"}),OR(LEFT(M2)={"a","b","c"})*4)
This worked well but now I need to modify the weightings across this range. My new goal is to count all instances of a,b and c in the range J2:AB2 but have M column a,b, c's = 2 and N:R column a,b,c's equalling 4. Letters other than a,b or c count as 0.
Example:
J2...K2...L2...M2...N2...O2...P2...Q2...R2...
A....B.....B.....C.....B.....D....B.....C.....E
the output of the above sequence should be
1+1+1+2+4+4+0+4+4+0 = 21
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.
I have three variables; Independent variables x & y, and dependent variable z. x & y can take the values 1,2,3 and can equal each other z can also take on values 1,2,3 but can never equal x or y. I tried using if/and statements to solve this (see below), but I would have to do it for each case and it exceeds Excels embedded function limit.
=If(and(B7=1,B10=1),riskduniform(E4:E5),if(and(B7=1,B10=2),3,if(and(B7=1,B10=3),2,if(and(B7=2,B10=2),riskduniform(1,3),if(and(B7=2,B10=1),3,if(and(B7=2,B10=1),3,if(and(B7=3,B10=3),riskduniform(1,2),if(and(B7=3,B10=1),2,if(and(B7=3,B10=2),1)))))))))
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)),"")
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.
Here in "Grade" Column wants put a formula so that it will show First,Second,third and Fail. If the Mark is more than or equal 40 but less than 50 will reflect Third,if the mark is more than or equals to 50 and less than 60 will reflect Second, if the mark is more than or equals to 60 will reflect First and remaining less than 40 will reflect fail.
View 2 Replies View RelatedFromToGr Rate
90100A14.0
8689A23.6
8085B13.2
7279B23.0
6471B32.8
5663C12.6
This is part of a marks to grade and rating table. I have a list of students and the marks they obtained in an exam. I need a formula to put the grade and rate beside each name e.g. John; 92 marks; A1; 4.
I would like to link students for the 1st Nine Week list to the correct grade level as shown. I have attached a file.
View 2 Replies View RelatedI would like to prepare mid-term grades for my history class, and have three categories, which I would like to insert into a spreadsheet to calculate the grades, as follows:
Mid-term 1: 30%
Mid-term 2: 40%
essays: 30%
each of the three grades is a number from 1-100; the final answer will be a number which I can then convert to a letter grade.
I'm just not sure what function to use, in that last column, to get Excel to calculate the final answer/grade.
Let's just assume that Mid-term 1 is in column A; Mid-term 2 is in column B; and the essay grade is in column C. How can I create a function that will allow me to give the above percentages to the respective assignments, when calculating a grade?
I would like to have a student's current overall grade be automatically calculated and displayed as I enter the test scores and extra credit points over the semester, first in percent and then in letter grade.
I have it set up so that the percent is calculated based on the total number of points likely to be obtainable at the end of the semester. But if they have only taken two tests and one extra credit assignment, it takes the total of those and divides it by the semester's total possible, instead of just the two tests and extra credit total possible. (ps: I do not know how many extra credit columns or number of possible points I will have this early in the semester)
Also, how then do I set up a formula to translate that percentage into a letter grade based on A=90-100, B=80-89, etc.?
How can we convert time on a scale of 100 to a scale of 60?
View 3 Replies View RelatedI'm creating a Speech and Debate calculater that presents winners names by place according to their score. If you look at Tab HSSE Results it will read the inputed information from HS Speech English. I want the names of the students to appear in the correct ranking order in the respected grade rankings chart. I currently have a countif formula which reads the grade, but I think I need to link that with an index formula which I'm having problems with.
View 8 Replies View Related