
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Hello,
I recently created a spreadsheet of student scores from 5 tests, where i averaged exam scores, rounded them, had a letter grade column (using a formula) and a ranking column(using a formula) for a task
I have been challenged further and been asked to create a macro that will insert columns into my spreadsheet which will, I have been told that the macro should do the following in the column it creates:
 insert the test name (i.e test 7) into the appropriate cell
 insert zeros into all cells holding grades so i could put in the actual grades later
 repeat all equations for the cell if they are done fore the cell adjacent to it
Similar Excel Video Tutorials
Absolute & Relative Macro Fix Data
 Learn How To Use A Macro With Relative and Absolute References to Deal With Data With Variable Row Size! A MUST TRICK IF YOU USE RECORDED MACROS. ...
Excel VBA code From Internet
 Learn about: 1.Formatting a simple Static Report (Common Macro task in the working world) 2.Posting question to Mr Excel Message Board to ge ...
Relative & Absolute Macro Recorder
 Learn about: 1.Relative & Absolute button in the Developer Ribbon 2.The fact that the Relative & Absolute button governs selection m ...
Similar Topics
I need to create a macro that fulfill the following:
The spreadsheet has gotten high praise from the teacher. However, there was a test that the teacher gave and forgot to give you the grades for. Therefore, you need to insert another set of grades for each student. You are to do this by creating a macro that will insert a column into your spreadsheet. The macro should do the following in the column it creates:
1. Insert the test name (i.e. Test 6) into the appropriate cell
2. Insert zeros into all cells holding grades, so you can put the actual grades in later.
3. Repeat all equations for a cell if they are done for a cell adjacent to it. (.i.e. if the cell adjacent has taken the high grade from the column, then the macro should do this for the cell.)
4. Print the spreadsheet out.
See attached file for worksheet.
I have been trying for hours and I cant get this done, please help!
Hello,
So this summer i have been trying to make myself conversant with all aspects of MS Office by doing exercises passed on to me by a friend who took a class on this a couple of months back.
Over the past few days, i have been getting conversant with MS Excel and have been good with doing various arithmetic calculations using a formula and made a spreadsheet that would calculate the average of the first best test scores of 13 students, assign grades and rank the students.
The next part of the exercise involves creating a macro and this is what the instructions say:
The macro should do the following in the column it creates:
1.Insert the test name (i.e. Test 6) into the appropriate cell
2.Insert zeros into all cells holding grades, so you can put the actual grades in later.
3.Repeat all equations for a cell if they are done for a cell adjacent to it. (i.e. if the cell adjacent has taken the high grade from the column, then the macro should do this for the cell.)
I have uploaded the exercise i was working on over he
http://www.megaupload.com/?d=IIEYEJM5
Can someone please assist me with this. I have no idea on what to do
Thanks!
Hello,
So this summer i have been trying to make myself conversant with all aspects of MS Office by doing exercises passed on to me by a friend who took a class on this a couple of months back.
Over the past few days, i have been getting conversant with MS Excel and have been good with doing various arithmetic calculations using a formula and made a spreadsheet that would calculate the average of the first best test scores of 13 students, assign grades and rank the students.
The next part of the exercise involves creating a macro and this is what the instructions say:
The macro should do the following in the column it creates:
1.Insert the test name (i.e. Test 6) into the appropriate cell
2.Insert zeros into all cells holding grades, so you can put the actual grades in later.
3.Repeat all equations for a cell if they are done for a cell adjacent to it. (i.e. if the cell adjacent has taken the high grade from the column, then the macro should do this for the cell.)
I have uploaded the exercise i was working on over he
http://www.megaupload.com/?d=IIEYEJM5
Can someone please assist me with this. I have no idea on what to do
Thanks!
Hello, I have been working on a project to help track test grades to help out one of my teachers. I have made a macro that will do a number of things, one of them being insert a new column, name it and auto populate it with 0s for all students.
Example. The spreadsheet already has 5 tests on it, my teacher runs the macro and it will create a column named test 6 and fill the column down with 0s for all the students.
My issue is that to the right of the tests there is a column with a average formula for each student, it will take the average and drop the lowest grade. However, the formula will not update to include the newly inserted column.
EXAMPLES!
Code:
=IF(COUNT(B4:F4)=5, (SUM(B4:F4)MIN(B4:F4))/($B$271),AVERAGE(B4:F4))
Thats the average formula im using. At this time each student has 5 tests done, so if i insert a 6th i need the formula to become
Code:
=IF(COUNT(B4:G4)=5, (SUM(B4:G4)MIN(B4:G4))/($B$271),AVERAGE(B4:G4))
And if I insert and 7th i need it to become
Code:
=IF(COUNT(B4:H4)=5, (SUM(B4:H4)MIN(B4:H4))/($B$271),AVERAGE(B4:H4))
The average is in g4:g19 column, but gets shifted every time you add in a test.
Test one is b4:b19, test two is c4:c19 and so on.
Student names are a4:a19
I know I can have them run the error correction tool in excel, but i know there has to be a way to do it automatically and im missing it. I have spent hours trying to solve this, any help is appreciated.
Hello I have a list of grades they are 3a, 3b, 3c, 4a, 4b, 4c, etc up to 7a. An a is one grade than a b, and a b is one grade higher than a c. In one test a student may score a 4b and in another a 4a. I need a way to calculate the difference in "grades" e.g if a student scores a 4a in test 1 and a 5c in test 2 the difference is 1. Is there a way I can do this using custom formatting or will I have to assign values to the grades and insert extra columns to do the calculations. I need the simpliest way as I have to disseminate to my staff in my department.
many thanks for looking
S Woods
I've designed a simple spreadsheet to track grades using Excel 2003.
Each specific assignment/quiz grade for a student is in a separate column.
The beginning row of each class contains the highest grades possible for
each assignment/test/etc.
Sample:
Name Rank Cumulative Offset Gr1 Gr2
G3 G4.
1 Possible Score 260
100 20 40 100
2 Student 1 196 40
80 20 E 96
3 Student 3 206
86 20 30 90
4 Student 2 190 60
90 E E 100
..
To determine the grade for each student, I've created a formula (in the
"Rank" Column) that calculates a percentage of the cumulative highest score
possible.
This works fine, except sometimes I excuse a student from an assignment. To
compare their total scores to the cumulative highest score possible would be
unfair, so to accommodate for this, I've added a column labeled "Offset".
For each student in this situation, I put an "E" (for "excused") in
appropriate column. Then I manually put the corresponding high score in the
Offset column for any student who has been excused from a given assignment.
Here's the formula I'm using: =ROUND(((100*C2)/($C$1D2)),0)  Where
C2 is the total of all scores for a given student, $C$1 is the cumulative
highest score possible, and D2 is the offset value if any. (Finally I use
VLOOKUP to insert a letter grade in another column.)
QUESTION:
Is there a way to accommodate this "Excused" situation with a some sort of
conditional formula so I don't have to manually assign an "Offset" for those
students. (maybe with SUMIF?) For example: If "E" is a student's score
for a given assignment, then subtract the highest possible score for that
assignment. Or if a cell is not "E" then include the highest possible score.
Any suggestions would be greatly appreciated.
Thanks!
Dear all experts:
I'm a college teacher and have been using Excel to post students' various quiz/exam grades on the web. However, starting from this semester, we were asked to respect students' privacy of NOT sending/posting the grades of the entire class anymore.
Since I usually have several hundreds of students' grades to take care of each semester, it really becomes a burden if I had to look up each student's class taken/grade/email address and send out individual email notices. Therefore, I'm wondering if there's any solution in Excel's macro or programming capability to automatically send each individual student's grade to him/her once I have their grades calculated in Excel?
What I have in mind is that I'll have the following info in an Excel file:
Cloumn A: Student's name
Column B: Student's ID
Column C: Student's email address
Column D: Name of the course
Column E: Name of the test (e.g. quiz #1, midterm exam, final exam, ...etc.)
Column F: grade or score
Column G: percentile for the preceding grade in the class
Column H: course coordinator's email address
After I'm done with the grading process, I wish to use my email app (MS Outlook 2007) to automatically send each student with their perspective grade info in the following format:
Dear [Column A]:
For the test Column E] of the course [Column D] you've registered, your grade is [column F] with a [Column G]% among your classmates. If you have any question on your grade, please contact the course coordinator at [Column H], thanks.
Since I'm only an average Excel user w/o any knowledge on how to compose any codes/macros, I wonder if this request is doable withing Excel itself? I'll highly appreciate if I could get any help/suggestion from you. Many thanks in advance!
Best,
Marc
I have a row of grades. Each column represents an assignment and the assignments fall into one of 5 categories: Tests (30%), Quizzes (20%), Classwork(15%), Homework(15%), Final Exam(20%). Each assignment has a possible point value declared as well. The grade is determined by dividing the points they received by the possible points( 9/10, 87/100, 82/85, etc.) The overall grade takes that grade and mulitplies it by the category percent value, then adds all categories together. On sheet 2, I have the information in this format:
AB CD
1. poss.ptsgradeCategory%value
2. 10096Test.30
3. 108.5 Classwork.15
4. 8583Quiz.2
5. 10085Test.30
But on sheet 1, where I need to see the students' overall grades, I have the row of grades in columns titled by the categories for each assignment and the point value in in row 5: The Grades for each student are in rows 7 to 25.
 A ..   J  K  L  M  N  O 
4.  Test  Class  Quiz  Test  Class _ Quiz
5. . 100  10  100  85  20  100
6.
7.John  92  8.5  97  76  18  87
8.Mary  84  9  . 99  82  19  90
How can I write a formula to get the total grade in column B that takes into account the category percentage, the point value, and the fact that since the final exam isn't taken until the last day, that 20% has to be assumed so the overall grade doesn't show as only 80%? Actually, any category that doesn't have a grade in it must be assumed to be its complete percent until a grade is put in. Otherwise, the total grade will appear very low until a grade exists in each category.
ie: ((Grade on quiz/pt value*100) x .2) + ((Grade on Test/pt value *100)x.30)) + ((Grade on Classwk/pt value *100)x .15)+ (if no grade in a category, assume it to be full percentage until a grade is entered)..
Sleepless in Nashville...
Hi
I am trying to get a formula to work that will assign a grade to a cell
based on a numeric result. As as an example if a student scores anything
above 84.99 (grade recorded in Cell D8) the grade of HD is assigned by the
formula listed below.
=IF(D8>84.99,"HD",IF(D8>74.99,"D",IF(D8>64.99,"C",IF(D8>49.99,"P",IF(D8>42.99,"PC",IF(D8>=0.1,"F"))) )))
I need to be able to split these broad grades into upper and lower ranges,
for instance a student who scores 96 will get a grade of HD+ but the formula
above doesn't recognise this. Similarly a student might get 82 which equates
to a D+.
I need a formula that will lookup a grade in cell D8 and assign a letter
grade based on the following:
HD+ = >95; HD = >=90; HD = >=85; D+ = >=82; D = >=80; D = >=75; C+ = >=72;
C = >=70; C = >=65; P+ = >=60; P = >=55; P = >=50; PC = >=43; F = <=42
Any ideas would be greatly appreciated
Thanks Brian
I have tried every formula I can think of and can not get a correct average for test scores as they adjust to the percentage of the final grade. Is there any specific formula I am missing. Below is the spreadsheet I am trying to complete.
Student's Name
Homework
Test I
Test 2
Projects
Mid Term Exam
Final Exam
Final Average
Final Grade
10% of Final Grade
15% of Final Grade
15% of Final Grade
20% of Final Grade
20% of Final Grade
25% of Final Grade
Connaughton, Kenneth
95
92
93
95
94
95
94
I am just starting to try to use excel for the first time. I am a super rookie so be kind. I am trying to fix a problem and don't know where to look for the solution. This spreadsheet will be used to calculate grades of students in my class. I cannot figure out how to keep a blank cell from being assigned the value of "0" in a formula. I have a range of columns for test scores and a column for the total score. I also have a column for grade percentage. If I enter a new test and a student has not taken it yet, the blank cell is assumed to be a score of zero and their percentage goes down even though I have not given them a "0".
The fomula for the total piont for the test is =SUM(H10:N10) h through n is the individual test scores. I also have a lab score total =SUM(O10:AA10). the percentage is =((E10+F10)/(E$10+F$10))*100.
I hope this is enough information.
Hi All. Newish to excel and I'm looking for some help. Im creating a spreadsheet to track student scores. There will be 12 student tests each ending with a % grade (eg 72%). The percentages of all 12 tests will add up at the end of the semester minus the lowest % grade. If there are two matching lowest grades (eg: 10% and a second 10%) only one of the grades will be removed.
I hope that makes sense. I don't know if its even possible, but any thoughts would be greatly appreciated.
thanks
S
Damn, I hate exam analysis  and would appreciate any help:
I'm working with student grades and I have a column of grades (column A3A160). I need to create a number of statitsics from these:
1) Number of A grades
2) Number of B grades
3) Number of C grades
4) Number of D grades
5) Number of E grades
6) Number of F grades
7) Number of U grades
then I will need to work out the percentage of students who:
8) Got each grade
9) Who got A to C
10) Who got A to F
Many thanks to any help you can give, I know it is a lot of questions.
Hi there
I am trying to perfect a spreadsheet that will analyse the stats of grades. Their name, gender, predicted grade and actual grade are entered into columns A, B, C & D respectively, commencing in cell B38.
I am trying to perfect a formula that will tell me the grades of BOYS only that score D,M,P and FAIL (D=Distinction, M=Merit, P=Pass) and express it as a % of the BOYS only grades.
As a test I entered the grades & genders of 3 boys in cells A38D40, inputting D for distinction in all three actual grade colums. I also inputted grades for 4 girls too. In cell Y5 I used the following formula to calculate the % of boys getting a D as an actual grade (which should be 100% as all 3 boys got D).
=SUMPRODUCT(($B$38:$B$131="M"),($D$38:$D$131="D"))/ COUNTIF($B$38:$B$131,"?*")
The result of this formula is 42.9% which is the right result IF I wanted the % of BOYS & GIRLS that got a D. I am having brain freeze here  anyone want to point out the obvious and help me just to isolate the boys grades?
column A has 3,600 names (last, first), column B is the name of school for each name (30 schools), columns C,D,E,F and G are numbered scores for each of the names.
column I has 600 names (last, first), column J has lettered grades for different level (15 levels).
My problem: I want the names from column I compared to column A, giving me (in new columns) the 600 names with their applicable scores from columns C,D,E,F and G, sorted by column J (the letter grade).
My story...My wife is a physical eduction teacher responsible for 600 students from 30 different schools. There are 3,600 students in the test program. She has a list (converted to excel) of all 3,600 student with the appropriate scores. However, she needs the 600 names (covering the 30 schools) with their respective scores sorted by their 15 different levels. We don't need the name of the school (column B).
I laughed at myself last night as we were cutting and pasting all the different names to get them in multiple columns when it dawned on me there must be an easier way. Can anyone help us? I am new to excel so if you can be as specific as possible I would appreciate it.
Also, will we run into any problems if a student has his first name listed as Robert in one column but Rob in another with the same last name?
Thanks in advance.
I am creating a spreadsheet for teachers to enter final grades. I need the FINAL Column (which is I) to be empty until Grades are entered into C and E. Here is what I have so far: Column C = GRADE, Column D =SUM(C3*3), Colmn E= Grade, Column F=SUM(D3:E3), Column G =SUM(F3/4), Column H =SUM(G3*0.35), Column I =SUM(44.85+H3).
H3 is the final calculation in a series BEFORE they add 44.85 for the FINAL grade. Until grades are entered for each student however, H3 is blank. The problem is, when the spreadsheet is opened, column I lists the value of 44.85 in every row all the way down and I would like the value to appear to be nothing or 0 until there is a value in H3. That way no numbers at all will appear in column I until there is a grade in C & E.
I want my forumla to read something like this: If H3=0, then do not add the 44.85 (or 0 remains the value). If H3 HAS a value, then add the 44.85 for the FINAL grade.
Is there a way to do this?
PS I plan on hiding all the columns with forumula, so the teachers will see columns C, E, and I. FYIColumn A and B will be first and last name of student.
Hello again it's been a while. Hope you can help once again.
I have a spreadsheet where each row holds grades data for a student.
Each student has 3 columns of grades ( not adjacent to each other.)
I want to countif column A, C, or E contains an A* grade ( a "starred A") as opposed to an A grade (without the *)
I cant persuade it to differentiate between "A*" and "A" . The grades were generated from a vlookup table.
Sorry if this is poorly described.
I would like to prepare midterm grades for my history class, and have three categories, which I would like to insert into a spreadsheet to calculate the grades, as follows:
Midterm 1: 30%
Midterm 2: 40%
essays: 30%
each of the three grades is a number from 1100; 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 Midterm 1 is in column A; Midterm 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 did a bit of searching, but I came up with nothing that would help me out. Thanks in advance.
Hello Everyone!
I could use some help. Have patience, and speak slowly, I'm new to Excel.
I'd like to apply a curve to a column of student exam grades and have the curved grades displayed in the adjacent column. Here is how I'd like to curve:
I like to make a linear function out of two coordinate pairs.
First Pair (x1,y1) = (Highest Exam Grade, 99) = (H,99)
Second Pair (x2,y2) = (Class Exam Average, Desired Exam Average) =(C,D)
Slope = (99D)/(HC) = m
Linear Function = Yy = m(Xx) = Y99=m(XH)
Each student grade would = "Y" i.e. Y=m(XH)+99
Can someone help me out with this?
Thanks a ton in advance,
Bill
I have a row of data containing ranges for letter grades (ex. 049%, 5059% etc) and a second row containing the corresponding letter grade each numerical range recives (ex: F, D etc) how do i insert a lookup function into a workbook of student grades in order to automatically compute the letter grade each student recieves?
Good day,
I have a spreadsheet that in column E (from row 4 to row 82), when a cell is selected in this range, a data validation box opens up containing a list of possible test grades. In this case, the list has the grades 10+, 10, 10, 9+, 9, 9, etc...... The adjacent column, column F (from row 4 to row 82), has the previous test grade which are entered via the VLOOKUP command which is used against a list containing student information and their prior test grades, which are 10+, 10, 10, 9+, 9, 9, etc.....
What I would like to do is use conditional formatting on cells in column E such that if the grade in column E is higher, lower, or equal to the adjacent cell in column F, I get different formatting. For example, a higher grade would turn the cell in column E a green pattern, an equal grade a yellow pattern, and a lower grade a red pattern . In this case, a 10+ is higher than 10, a 10 is higher than a 10, etc......
I have tried various formulas for conditional formatting with little success. I even tired the simplest of all formulas (E4=F4) to debug my problem and that doesn't work. So, this tells me that there must be some trick/command I must use when doing cell comparisons when one cell has data using the VLOOKUP command.
Any help would be greatly appreciated. Thank you.
I created an answer sheet templete that would grade a test once you put in the student's answers. It looks like this
https://spreadsheets.google.com/spre...hl=en_US#gid=0
but imagine 12 sheets similar (but not exactly) to this (grades 1  12). Once I fill out the empty information, I just need to print it, not save it. There are multiple students I have to grade. So I inserted a macro that would clear the "student's answers" column quickly.
My boss wants me to collect the scores and names of the students as I grade them. How do I gather the name and scores of the students and put them in a different sheet automatically, when I basically delete their information once I clear their names using a macro? My original spreadsheet is more of a generator than data. What do I do?
I'm confused.
I have a problem where I am starting with three files.
1) "Upload Template"
2) "Round 1 Scores"
3) "Round 2 Scores"
There are four fields that matter amongst the three files.
1) StudentTest ID (from "Upload Template")
2) Test Score (from "Upload Template")
2) StudentTest ID (from both "Round 1 and 2")
3) Test Score (from both "Round 1 and 2")
Looking to compare the StudentTest ID from "Upload Template" and both "Round 1 Scores" and "Round 2 Scores", then if there is a match, pull the Test Score from either the "Round 1 Scores" or "Round 2 Scores" file and insert that score into the Test Score column in "Upload Template".
I am also looking to format the StudentTest ID column in both "Round 1 and 2" columns so that if a match is not found, the field turns red. This way, if there is not a match, it is easy to find the row and figure out the problem (for example: if a kid filled out the studenttest id field incorrectly on either the round 1 or round 2 test).

As it stands now, we are inserting the fields we need from "Round 1 and 2" files into the "Upload Template" and inserting the StudentTest ID and Test Score as columns at the end of the existing table. This is what the code looks like that I am currently running:
=IF(ISNA(VLOOKUP($I2,$L$1:$M$2000,2,FALSE)),"",(VLOOKUP(I2,$L$1:$M$2000,2,FALSE)))
Here are the breakdowns of the columns as listed in the above code:
Column I = StudentTest ID from "Upload Template"
Column L = StudentTest ID from inserted data from "Round 1 and 2 Scores"
Column M = Test Score from inserted data from "Round 1 and 2 Scores"
The code sorts through correctly and enters a score into the "Upload Template" Test Score field, which is Column J.
Here is the problem with doing this:
1) Every year the amount of students changes, so the "...:$m$2000" item needs to be dynamic in case more than 2000 students take the test.
2) We have to manually copy and paste the StudentTest ID and Test Score into the "Upload Template" from the "Round 1 Scores" and "Round 2 Scores" files, then parse the information, then delete those rows so the "Upload Template" file is in the exact format the University of Illinois wants.
3) When there is no match, it is hard to find which column did not find a match, which is why I am asking for a way to turn the unmatched StudentTest ID cell red from the "Round 1 and 2 Scores" files.
In a perfect world, I would be able to open "Upload Template" run a macro and have everything open, copy, format and be completed and ready to save. If that cannot be accomplished, I am ok with copying in the data from "Round1 and 2" into the "Upload Template" as we have been doing.
Thank you ahead of time for any help.
In Col A, I have 43 grades on a particular exam. From this column I have generated In Cells B a Histogram which gives me six frequencies of scores between increasing values.
Now I need to use the information from Col A and Cells B to populate Column C with the grades for each student, so that every grade falls within its appropriate values.
I am using Excel 2010.
Can anyone help me, please?
And thank you, in advance.
rstanleyg
I'm trying to sort out a spreadsheet to track student progress. Students can get the following 'grades': 4a,4b,4c,3a,3b,3c and so on downwards. (these I want to give a numerical value to)
So in the attached spreadsheet the grade the student has got is in Column I. The target grade the student needs to get is in Column C. And then I will have a sum in Column J which works out the difference between the target grade and the grade the student achieved.
On the same spreadsheet, in different columns I use the formula:
=LOOKUP(G3, {0,6,10,14,18,24,28,32,36}, {"U","G","F","E","D","C","B","A","A*"})
To turn numerical grades into letters. I've tried altering this, but it doesn't work.
So how can I give these grades in Column I a numerical value, something similar to the above formula would be perfect; or anything you can come up with would be greatly appreciated
Many thanks

