Macro To Insert Columns 


Macro To Insert Columns  Excel 
View Answers 
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
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 Tutorials
How to Add, Remove, and Rearrange Columns and Rows in Excel
Adding, removing and rearranging columns is as easy as resizing. You just select your columns or rows, right click ...
Adding, removing and rearranging columns is as easy as resizing. You just select your columns or rows, right click ...
Install a Macro into an Excel Spreadsheet
This tip will show you how to copy an Excel Macro into your workbook or spreadsheet. You will learn the different l ...
This tip will show you how to copy an Excel Macro into your workbook or spreadsheet. You will learn the different l ...
Run a Macro When you Click a Button in Excel
Make a macro run when you click a button in the worksheet in Excel. This allows you to create a button and put it a ...
Make a macro run when you click a button in the worksheet in Excel. This allows you to create a button and put it a ...
Run a Macro from Another Macro in Excel
I will show you how to run a macro from another macro in Excel. This means that you can run any macro when you ne ...
I will show you how to run a macro from another macro in Excel. This means that you can run any macro when you ne ...
Helpful Excel Macros
Name Worksheets Based on Cell Contents
 This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
 This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Highlight the Column of the Selected Cell
 This macro will highlight the column of the active cell. This will fill the column of the active cell with a solid back
 This macro will highlight the column of the active cell. This will fill the column of the active cell with a solid back
Reverse Row or Column Order in a Worksheet
 This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
 This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Highlight the Row and Column of the Selected Cell
 This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w
 This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w
Hide Specific Comments in Excel  Comments Will Still Display on Hover
 Hide specific comments in Excel with this macro. Comments are still visible on hovering over the cell that contains the
 Hide specific comments in Excel with this macro. Comments are still visible on hovering over the cell that contains the
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!
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!
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!
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 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
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!
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'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...
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 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
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.
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.
Afternoon,
I am using the formula
=VLOOKUP(Fl.Grades!H2,scores!$A$2:$B$8,2) to turn school grades into point scores
Which is looking up correctly, but when the lookup cell is empty it is giving an error message, so I changed it to
=IFERROR(VLOOKUP(Fl.Grades!H2,scores!$A$2:$B$8,2),"")
This works in that it converts the grades to points, and if there is now grade it then leaves the cell blank. However, in a later column I want to count up the number of times a point score is in these lookup cells, so I have used
=COUNTA(H2:X2)
However, this appears to be counting all the cells, as they have a formula in rather than just the ones which have point scores in.
Any help would be gratefully appreciated.
Thanks
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?
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?
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.
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.
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.
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 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.
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 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 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.
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.
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.
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
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
Hello all.
I am wanting a macro to insert a column at every change in a given row. Using row 3, it will look like this:
07...07...08...10...10...14 (dots representing cell border)
and I want a new column between each change.
so it would look like
07...07...insert column...08...insert column...10...10...insert column...14
Hope that is clear.
I am really poor at VBA, but I did put the following together by altering a row insert macro. Maybe it will be helpful to you.
Code:
Thanks,
Joseph
I am wanting a macro to insert a column at every change in a given row. Using row 3, it will look like this:
07...07...08...10...10...14 (dots representing cell border)
and I want a new column between each change.
so it would look like
07...07...insert column...08...insert column...10...10...insert column...14
Hope that is clear.
I am really poor at VBA, but I did put the following together by altering a row insert macro. Maybe it will be helpful to you.
Code:
Sub AddColumns() Dim c As Long For c = Cells(3, Columns.Count).End(xlLeft).Column To 2 Step 1 If Cells(3, c) Cells(3, c  1) Then Columns(r).Insert Shift:=xlRight Next c End Sub
Thanks,
Joseph
Hi, I am trying to develop a formula that will only fill the final grade of a
student in the event that there are two grades allocated in the results
column. I have the formula to assign the letter grade worked out, but because
the value of the source cell is '0', Excel automatically assigns a 'F' in the
cell (B13) (and rightly so as the formula is simply doing what it is told).
I have 2 seperate scores, one each stored in cells D13 and E13, these are
summed to give a total score out of 100 and this result is stored in cell
C13. Based on the following formula :
=IF(C13>84.99,"HD",IF(C13>74.99,"D",IF(C13>64.99,"C",IF(C13>49.99,"P",IF(C13>42.99,"PC",IF(O7>=0,"F" ))))))
I need the result in the format of a letter grade in cell B13 based on the
formula above, however I only want a value displayed in cell B13 if the
criteria below is met:
A grade is assigned in each of cells D13 and E13.
If anyone has an answer it would be greatly appreciated.
Brian
student in the event that there are two grades allocated in the results
column. I have the formula to assign the letter grade worked out, but because
the value of the source cell is '0', Excel automatically assigns a 'F' in the
cell (B13) (and rightly so as the formula is simply doing what it is told).
I have 2 seperate scores, one each stored in cells D13 and E13, these are
summed to give a total score out of 100 and this result is stored in cell
C13. Based on the following formula :
=IF(C13>84.99,"HD",IF(C13>74.99,"D",IF(C13>64.99,"C",IF(C13>49.99,"P",IF(C13>42.99,"PC",IF(O7>=0,"F" ))))))
I need the result in the format of a letter grade in cell B13 based on the
formula above, however I only want a value displayed in cell B13 if the
criteria below is met:
A grade is assigned in each of cells D13 and E13.
If anyone has an answer it would be greatly appreciated.
Brian
I have a sheet of grades in columns. The grades are "raw scores" and don't reflect the real grade since the assignment may have only been worth 80 points. So the 72 in that column is actually a 90%. I want to have a menu option that allows an immediate change of all grades to their percent score, and another that changes them back to their raw scores. I can do this by pasting formulas in that take the raw score from another area where they were copied when the menu selection is activated, divide it by the maximum points possible and multiply it by 100, but then I can't add or change any grades without first copying the raw scores back over the formulas ... Is it possible to have formulas "in the background" that allow input into the same cell? I have seen a program that does this. The user can actually put in raw scores and they instantly change to the percent score if that is the selected view. But when a cell is selected, it shows the raw score, and may be changed, until it is not the active cell, where it changes back to the percent. Is that possible with Excel?