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 ...
Insert Check Mark in Excel  3 Ways  Incl. VBA and UDF
This tutorial goes beyond other simple checkmark tutorials. Here, I'll show you 3 methods to insert a checkmark i ...
This tutorial goes beyond other simple checkmark tutorials. Here, I'll show you 3 methods to insert a checkmark i ...
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'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
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 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?
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
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 was asked to compile student's results and each student takes at least 7 subjects and at most 12. The task is to calculate grades,weighing grades and summing
up the weights of 7 best performed subjects.
I used a nested iffunctions to assign grades to scores and also to weigh the grades, If cell C2 is the reference one, then
=IF(A2>80,"A",IF(A2>60,"B",IF(A2>40,"C",IF(A2>30,"D","F")))) THIS ASSIGN GRADES,
=IF(B2="A","1",IF(B2="B","2",IF(B2="C","3"IF(B2="D","4","5")))) THIS WEIGHS GRADES,
The problem comes on how to sum up the weights of the 7 best performed subjects. May you please help me?
Hello again  as I mentioned in previous help requests, I use Excel to manage student grades. This forum has helped me greatly!
Now I am asking:
I keep paper grades recorded in multiple columns. 15 Columns with letter grades. Is it possible to create a formula for these columns that will average the grades using the following numeric values AND will average only the highest 10 grades?
A  4 ; B+  3.5 ; B  3 ; C+  2.5 ; C  2 ; D+  1.5 ; D  1 ; F  0
Thanks in advance!
Now I am asking:
I keep paper grades recorded in multiple columns. 15 Columns with letter grades. Is it possible to create a formula for these columns that will average the grades using the following numeric values AND will average only the highest 10 grades?
A  4 ; B+  3.5 ; B  3 ; C+  2.5 ; C  2 ; D+  1.5 ; D  1 ; F  0
Thanks in advance!
I need to create a spreadsheet in my workbook where I can type a single student's name and their class number and then retrieve their total grade numbers and letter grade. Their name is stored and their grade is calculated on one of the sheets in the same workbook.
I often get requests from students to see their grades, but I need to keep the other student grades private.
The spreadsheet recording the student grades is divided into two classes per day, for Mondays through Saturdays. The student names are entered into the A column, and each table is a different class. There are three cells above the student names in each table: "CLASS XXXnnn  n" (XXX are the letters of the class number, i.e.: "ENG101  5"); The day name and the time is on the line below this (i.e.: "Friday AM"), and below this is the word "Students". Under this, is listed, the students' names in alphabetical order: last name, then first divided by a comma. Each range is 40 blanks long for student names.
The target sheet has two tables: one that lists the percentage groups and their corresponding letter grades, and the second that lists in a horizontal line, four fields: The Student Name and Course Number that I want to fillin, and the Current Grade Average (number) and its corresponding Current Letter Grade for that number.
I think I might use VLOOKUP, but haven't found suitable documentation for making this work. Any ideas?
I often get requests from students to see their grades, but I need to keep the other student grades private.
The spreadsheet recording the student grades is divided into two classes per day, for Mondays through Saturdays. The student names are entered into the A column, and each table is a different class. There are three cells above the student names in each table: "CLASS XXXnnn  n" (XXX are the letters of the class number, i.e.: "ENG101  5"); The day name and the time is on the line below this (i.e.: "Friday AM"), and below this is the word "Students". Under this, is listed, the students' names in alphabetical order: last name, then first divided by a comma. Each range is 40 blanks long for student names.
The target sheet has two tables: one that lists the percentage groups and their corresponding letter grades, and the second that lists in a horizontal line, four fields: The Student Name and Course Number that I want to fillin, and the Current Grade Average (number) and its corresponding Current Letter Grade for that number.
I think I might use VLOOKUP, but haven't found suitable documentation for making this work. Any ideas?
I have recorded a macro to insert multiple columns throughout a spreadsheet and I got the error: Compile Error: Wrong number of agruments or invalid property assignment.
I deleted all the code for my selecting cells as I scrolled to the right as I was adding columns and it is down to only selecting the applicable columns and inserting the number of columns in to the right of the selected column. I do not see where there is an error in the code. I do not get a highlight or underlining of text to indicate the error.
Thanks for your help.
Rodney Jorgensen
Sub Columns()
'
' Columns Macro
' Inserting Blank Columns
'
'
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("N:N").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("R:R").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("V:V").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AB:AB").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AF:AF").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AJ:AJ").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AO:AO").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AY:AY").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BC:BC").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BG:BG").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BJ:BJ").Select
Selection.Insert Shift:=xlToRight
Columns("BL:BL").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BP:BP").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BX:BX").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("CD:CD").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("CI:CI").Select
Selection.Insert Shift:=xlToRight
Columns("CN:CN").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("CT:CT").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("DF:DF").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("EV:EV").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("EZ:EZ").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FD:FD").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FH:FH").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FL:FL").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FP:FP").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FW:FW").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("GK:GK").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("GO:GO").Select
Selection.Insert Shift:=xlToRight
Columns("GS:GS").Select
Selection.Insert Shift:=xlToRight
Columns("GV:GV").Select
Selection.Insert Shift:=xlToRight
Columns("GY:GY").Select
Selection.Insert Shift:=xlToRight
Columns("HB:HB").Select
Selection.Insert Shift:=xlToRight
Columns("HF:HF").Select
Selection.Insert Shift:=xlToRight
Columns("HI:HI").Select
Selection.Insert Shift:=xlToRight
Columns("HL:HL").Select
Selection.Insert Shift:=xlToRight
Columns("HO:HO").Select
Selection.Insert Shift:=xlToRight
Rows("1:1").Select
Range("HI1").Activate
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Rows("11:11").Select
End Sub
I deleted all the code for my selecting cells as I scrolled to the right as I was adding columns and it is down to only selecting the applicable columns and inserting the number of columns in to the right of the selected column. I do not see where there is an error in the code. I do not get a highlight or underlining of text to indicate the error.
Thanks for your help.
Rodney Jorgensen
Sub Columns()
'
' Columns Macro
' Inserting Blank Columns
'
'
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("N:N").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("R:R").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("V:V").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AB:AB").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AF:AF").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AJ:AJ").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AO:AO").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("AY:AY").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BC:BC").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BG:BG").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BJ:BJ").Select
Selection.Insert Shift:=xlToRight
Columns("BL:BL").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BP:BP").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BX:BX").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("CD:CD").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("CI:CI").Select
Selection.Insert Shift:=xlToRight
Columns("CN:CN").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("CT:CT").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("DF:DF").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("EV:EV").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("EZ:EZ").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FD:FD").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FH:FH").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FL:FL").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FP:FP").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("FW:FW").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("GK:GK").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("GO:GO").Select
Selection.Insert Shift:=xlToRight
Columns("GS:GS").Select
Selection.Insert Shift:=xlToRight
Columns("GV:GV").Select
Selection.Insert Shift:=xlToRight
Columns("GY:GY").Select
Selection.Insert Shift:=xlToRight
Columns("HB:HB").Select
Selection.Insert Shift:=xlToRight
Columns("HF:HF").Select
Selection.Insert Shift:=xlToRight
Columns("HI:HI").Select
Selection.Insert Shift:=xlToRight
Columns("HL:HL").Select
Selection.Insert Shift:=xlToRight
Columns("HO:HO").Select
Selection.Insert Shift:=xlToRight
Rows("1:1").Select
Range("HI1").Activate
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Rows("11:11").Select
End Sub
I'm a teacher, and I'm trying to set up a simple sheet to track my grades. I have the basic framework set, but I need a little help with a couple of details  I hope someone can help.
To start, I set up a table with:
Column A: Grades AF (Rows 112, with B+, B, etc.)
Column B: Corresponding grade points (A=4, A=3.7, A/B+=3.5, B+=3.3, etc)
My students have three exams and a participation grade. The exams are essays, so they only receive a letter grade.
Next to each name, I have four lines, two colums each. I've put in a dropdown for a letter grade for each exam and participation, and entered a VLOOKUP to pull the correct grade point next to each grade.
So here's what I'm trying to do:
1. Create a formula calculating an average of all three grades, with the exams weighted 30% each and the participation weighted 10%. (I should mention here that I am NOT a math teacher!)
2. Once I have that weighted average, I need a formula that will select the grade from my vlookup columns.
Obviously, most of the averages won't be exact matches to my grade points, so I need my formula to select the highest grade for the average. Therefore, if the average is 2.9, my returned result will be "B"(2.7), not "B"(3.0). I may adjust grades up and down, but I want to have a calculation of the hard numbers for my consideration.
I hope this isn't too much to ask, any help would be greatly appreciated.
To start, I set up a table with:
Column A: Grades AF (Rows 112, with B+, B, etc.)
Column B: Corresponding grade points (A=4, A=3.7, A/B+=3.5, B+=3.3, etc)
My students have three exams and a participation grade. The exams are essays, so they only receive a letter grade.
Next to each name, I have four lines, two colums each. I've put in a dropdown for a letter grade for each exam and participation, and entered a VLOOKUP to pull the correct grade point next to each grade.
So here's what I'm trying to do:
1. Create a formula calculating an average of all three grades, with the exams weighted 30% each and the participation weighted 10%. (I should mention here that I am NOT a math teacher!)
2. Once I have that weighted average, I need a formula that will select the grade from my vlookup columns.
Obviously, most of the averages won't be exact matches to my grade points, so I need my formula to select the highest grade for the average. Therefore, if the average is 2.9, my returned result will be "B"(2.7), not "B"(3.0). I may adjust grades up and down, but I want to have a calculation of the hard numbers for my consideration.
I hope this isn't too much to ask, any help would be greatly appreciated.
Hi I am new to this forum so if I have put this in the wrong place or it has been raised before I appoplogise.
I have a spread sheet that I want to write a macro to perform the following.
1. Insert a new column every other column. (Have achieved this but cant lik to the next bit)
2. Insert the trim formula down all of the new columns refering to the cell in the (original) column to its right.
This will be to run across 120 columns and down about 300 rows. For the purpose of helping me it wouldnt need to be that big as I can adapt any help that can be offered back into my spreadsheet.
Any help would be really appreciated.
I have a spread sheet that I want to write a macro to perform the following.
1. Insert a new column every other column. (Have achieved this but cant lik to the next bit)
2. Insert the trim formula down all of the new columns refering to the cell in the (original) column to its right.
This will be to run across 120 columns and down about 300 rows. For the purpose of helping me it wouldnt need to be that big as I can adapt any help that can be offered back into my spreadsheet.
Any help would be really appreciated.
Hi all. This is probably a simple request but it has me stymied.
I'm producing endofyear student grades as an XLS spreadsheet. Thanks to some helpful advice from this forum
grades are all converting to numbers nicely, being totaled correctly then converted back into final grades. So far
so good.
Now I want to take the final grade and display it in another cell beside the student's name. So I'm using a simple
=h67 to transfer the info to the new cell. My supervisor cuts this info and pastes the grade into his master spread
sheet. However in practice he's getting my formula rather than the grade.
So my question is, how do I take the (A+, A, A, etc) result from the original formula and convert that into a fixed
and immovable grade?
I'm producing endofyear student grades as an XLS spreadsheet. Thanks to some helpful advice from this forum
grades are all converting to numbers nicely, being totaled correctly then converted back into final grades. So far
so good.
Now I want to take the final grade and display it in another cell beside the student's name. So I'm using a simple
=h67 to transfer the info to the new cell. My supervisor cuts this info and pastes the grade into his master spread
sheet. However in practice he's getting my formula rather than the grade.
So my question is, how do I take the (A+, A, A, etc) result from the original formula and convert that into a fixed
and immovable grade?
I am trying to create a tracking sheet that when I enter nonnumeric data (i.e. Y or N) excel can calculate a students grade.
Each student will complete a series of tasks. If they complete that task they get a "Y", and if not, they get a "N". There are three different levels, all in different cells. P, M and D
I want to be able to work out their final grade. In order to achieve a P, the student has to achieve all "P" grades, in order to achieve an M all "M" and "P" grades and a D all "D" "M" and "P" grades
I have seen this before but cannot remember the formula. Please help. I have included a spreadsheet with a dummy for all three circumstances and I need to put the final grade under the "grade" column
Thanks Book1.xls
I've created a macro that will take data from one spreadsheet and insert it into a certain column in a new spreadsheet. As of now, the macro places the data from the old spreadsheet into column C of the new spreadsheet, by creating a new row inbetween column B and the previous column c (i.e. pushing whatever used to be in column c into column d, etc.) What I would like to do instead is have it insert the data from the old spreadsheet to the right of whatever data is currently in the new spreadsheet. For example, in the new spreadsheet if there's data in column AF, the macro would insert the set of data in column G. The problem is I don't know how to get the macro to recognize wherever the data ends, so that it will insert the data to the row after that. This way, each day when I run the macro it will keep inserting data to the right, so that when I open the spreadsheet, column A will have the oldest data and as we move across the spreadsheet, the data will be the newer/just entered material.
the reason I want to do this is because my next step is going to be to create a graph using the data. Each column represents a certain day, so I'd like the data to be oldest to newest, so that when I create the graph it will represent the data as it's been moving over time. How it inserts the data now would make the graph inverted.
Any help on VBA coding or methods I could try would be extremely appreciated!
the reason I want to do this is because my next step is going to be to create a graph using the data. Each column represents a certain day, so I'd like the data to be oldest to newest, so that when I create the graph it will represent the data as it's been moving over time. How it inserts the data now would make the graph inverted.
Any help on VBA coding or methods I could try would be extremely appreciated!
I would like to thank anyone reading this, first off, for bothering. Thank you.
Background:
I am making a spreadsheet for a college department, tracking student statistics for the last ten years. There are rows for test scores, graduates, statistical information, and others. One of the cores of this is a section detailing the letter grades for each class the person participated in.
What I am trying to do:
There is a column for each class. I have entered colorcoded (through conditional formatting) grades for each student. What my supervisor would like is a percentile average of students who gained which grade by the color they were assigned.
A = Green
B, C = Yellow
D, F, W, V = Pink
I am sure I could some up with a COUNTIF for the entire area, but I only want the percentage to include those students who HAVE a grade in that course.
To use a short example say I have
A
A
B
C
A
Green= ___% Yellow=____% Pink=____%
I would like to have seperate percentages for each color grouping, only counting the 5 with grades and not the 8 spaces there are in total.
I apologize if none of this makes sense, but I can't seem to put to words what I'm looking for. *sheepish smile* If this is impossible, I am very open to suggestion. Thanks again!
Background:
I am making a spreadsheet for a college department, tracking student statistics for the last ten years. There are rows for test scores, graduates, statistical information, and others. One of the cores of this is a section detailing the letter grades for each class the person participated in.
What I am trying to do:
There is a column for each class. I have entered colorcoded (through conditional formatting) grades for each student. What my supervisor would like is a percentile average of students who gained which grade by the color they were assigned.
A = Green
B, C = Yellow
D, F, W, V = Pink
I am sure I could some up with a COUNTIF for the entire area, but I only want the percentage to include those students who HAVE a grade in that course.
To use a short example say I have
A
A
B
C
A
Green= ___% Yellow=____% Pink=____%
I would like to have seperate percentages for each color grouping, only counting the 5 with grades and not the 8 spaces there are in total.
I apologize if none of this makes sense, but I can't seem to put to words what I'm looking for. *sheepish smile* If this is impossible, I am very open to suggestion. Thanks again!
Hi All  I can't figure out how to assign weights to grades in the event that one or more students don't have grades in one or more of the weight categories. For example, a student might enroll late in a school term and, therefore, may have a null grade in a "quiz" or "test" category.
Each student's total grade points are converted to percentages and stored in these columns (every 3rd col from S:AE):
S V Y AB and AE
where S = participation % earned
V = assignments % earned
Y = special projects % earned
AB = tests % earned
AE = quizzes % earned
The weights could go anywhere. They are currently in:
D3 = 15% (15% of the total grade is for participation)
D4 = 25% (25% of the total grade is for assignments)
D5 = 25% (for special projects)
D6 = 25% (tests)
D7 = 10% (quizzes)
Currently, if any cells in the points possible columns contain 0, then I'm changing the % earned cells to null, such as in this formula:
=IF(T18>0,(U18/T18)*100,"") 'if there are points possible, then convert to percent, otherwise make null
If a student has a null in any weight column, then his/her total percent column should omit that column and only use the other columns for calculating the total average or percent.
The following thread has been helpful to me, but I am unable to adapt it to this problem. Thanks!
http://www.excelforum.com/excelgene...umproduct.html