I have a list of students who achieve grades A-G in subjects and I have organised these into columns. I then need to be able to be able to count a total of how many grades they have achieved, how many As, Bs etc. So far I have managed this but...I need to be able to attach different values to the grades. Some subjects are worth 0.5, some 1 and some 2. I cannot work out how to count if they achieve a grade A worth 0.5 + a grade B worth 1 etc.
View 9 RepliesI have a work problem that I'm not sure which functions to use. I'm creating a grade calculator...here is the problem:
I have 18 total lessons (cells A1:A18). Each lesson is worth either 3 points or 0 points. Of the 18 lessons, only 10 count towards my grade. What functions do I use to count only 10? the countif function? Do I use a nested IF?
remember that of the 18 lessons, only 10 (any 10, not just the first 10) count.
I've got all the grades sorted out for the Principal, she is now looking for me to tally up all the students who got straight 'A' grades. I know there is going to be anotehr combination here but I'll start with the basics of straight 'A's
The screen below shows how the data is presented although I've slimmed down the headings to fit better.
How do I insert a formula to look at each row to see where based on the subjects taken, there are straight 'A's
************************************************************************>Microsoft Excel - Y13 & 14 Combined Worksheet.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutZ6=
ABCDEFGHIJKLMNOPQRSTUVWXYZ1ArtArt*2BiologyBSChemistryD&TDramaEconomicsE.*LitFrenchGeographyHistoryH.*EconICTICT2IrishLatinMathematicsMaths*PureMusicPhysicsPoliticsPsychologyR.ESociologySpanish2********A****A**********A*3********AA*A*******A******4******A*C*****D****B******5***********B**********BAA*6*******C***D************BBSheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have few grades and conditions on which rate will populate in the D1 cell.
Tried at bit
=IF(OR(A1="x10",A1="x20"),"150",IF(A1="x30","240",IF(A1="x40","190",IF(A1="x60","340","50"))))
GradeConditionsRates
x10offshore150
x10Onsite320
x20offshore150
x20High Skill170
x20Onsite320
I am trying to average letter grades in a spresdsheet my formula seems to work, but if any of the columns in the spreadsheet are totally blank I get #DIV/0! in the cell where I should see the average grade. Is there anyway to stop this from happening. This is the formula I am using.
=CHAR(INT(SUM(IF(E21:E150<>"",CODE(E21:E150))/COUNTA(E21:E150)))) assay entered.
I have an excel spreadsheet with my students' grades on it. I want to be able to click on a student's name and have a list of all the assignments, along with only that student's corresponding grades, transposed on a new sheet so that I can print out to give to a parent.
View 13 Replies View RelatedI am trying to figure out a simple formula on determining class for students according to their achievement /grades. There are 9 classes altogether ( 7A, 7B, 7C, 7D, 7E, 7F, 7G, 7H and 7J). There are three columns for subject ( MATHS, ENGLISH, SCIENCE). So lets say the student get Grade A, A , A : The student will belong to Class 7A.
As attached.. TEST PSR.xls
I am attempting to create an IF or IF(AND formula that I can use to display the letter grade I earned based off of a certain percentage criteria.
"A" D11>=93%
"A-" D11>= 90%, D11=87%, D11=83%, D11=80%, D11=77%, D11=73%, D11=70%, D11=67%, D11=63%, D11=60%, D11
I need to calculate the average of 5 grades from A,B,C,D, and E. However I want it to the average even if any number of grades (i.e. up to 5) are entered.
e.g
grade1 grade 2 grade 3 grade 4 grade 5
A A C B E
I have assigned a vlookup table to convert to numerical values, and got it to calculate the average, but it falls apart when any grade is missing.
I have used the iferror command on the different calculations, but when i put them to gather into one large string, again it falls apart.
I am having trouble coming up with a formula to calculate the weighted average of students Finals grades. This is the format my sheet is in to give u an idea.
-----------------------------------------------------------------------
Test # 1 Test # 2Test #3Assignment #1Assignment #2Final Test Final MarkPossible Points:1001001001010100Percent Worth:20%20%20%5%5%30%Students:Anderson, Ken6473788768 ?Carter, Bill72768473.587 ?Dubeers, Ashley84887610590Jackson, Kevin7666549873Parker, Jerome82657679.560Remini, Leah969290101089Thompson, Chuck5042806.5760Vaughn, Sarah7265658970
----------------------------------------------------------------------- -
I have to come up with students averages based on the mark, the percent each grade is worth and the maximum amount of points its worth. Anyone know the formula or how to calculate the final grade using all of these factors?
I am in need of some more assistance regarding finding out a formula to find out the average grade of a pupil which ranges from letters 'A to E. This maybe be a simple task for experts, but unfortunatley spreadsheets are not my strong point. D7:D14 is the coloum that i wish to have an average of the grades acheived by each individual pupil in thier correspongin rows (on the right hand side of D7:D14) http://i41.photobucket.com/albums/e2...2/example2.jpg
View 5 Replies View RelatedNeed to calculate different percentiles based on different grades. how do I put an 'if' loop or anything else that will work for this.
GradeAmount
B23
B22
B35
B36
B48
B49
B510
B511
B615
B23
B37
B614
Need function for the table below
Percentile10%25%50%60%75%90%
B2
B3
B4
B5
creating a function to turn a percent (0-100) into a letter grade, I am creating a gradebook spreadsheet.
I think VLOOKUP is the way to go here, but I do not understand vlookup at all no matter how many times I try to look at demos or read how-tos.
I created two columns, one with what the grade entails (93-100 , 90-93 and so on) and the next column has A, A-, etc. But I can't get vlookup to work, any suggestions? I am pretty noob when it comes to excel.
I have created a gradebook and it has several types of workbooks for the student grades. 30% of the grades are calculated in different workbooks. The main gradebook is where the teacher places the mid term, final term grades. In the same workbook the teacher must place the grades for the following areas. (Participation Grade, Following Rules, Homework Grade, and Monthly Test). Now this is my problem!!! In the main grade workbook I need to automatically place the grades from the other workbooks.
Example: ='Participation English P-6'! ( Cells - AN84 for July, AN160 for September, AN274 for January, and AN350 for MArch. I tried to use = COUNTIF(AN84:AN160,AN274,AN350) but it will not work. I can get one grade at a time by using ='Participation English P-6'!AN84 , which will give me the grade in the main workbook that I need. But I don't want to change everytime the grade period comes around. forumal that will give me grades from the other workbooks at the grade time from these cells?
a formula that would take letter grades (A, B, C, D, and E) and produce an average letter grade?
For example, a student gets a D on one assignment and a B on another. I want a formula that will produce "C".
I am trying to calculate final grades with weighted averages. However for the final grade I need to drop the lowest of the test grade which come after the bold line to the right, making the total relative weight of Tests, the weight of 2 tests. How would I go about calculating that with a SumProduct formula? I do not need to drop grades from B4:K4, however from L4:N4 I need to drop the lowest grade for my final grade, which needs to be rounded to the nearest integer. The relative weight of each assignment is given in Row 1.
View 3 Replies View RelatedUsing Excel 2003,
I'm trying to come up with a formula that will take the students Total Pct. (column G, G12 is first cell & G41 is the last) from the worksheet called Juniors and find their Total grade (Column H, H12 is first & H41 is last) from the worksheet called Total Days. The % column on the Total Days worksheet is A (cells A10:A22) and the Grade column is B (cells B10:B22)
Juniors worksheet
Tot. Pct.
Tot.Grade
119.5%68.3%90.2%80.6%78.0%70.7%95.1%165.2%95.1%200.0%
Total Days worksheet:
%Grade99A+93A91A-89B+83B81B-79C+73C71C-69D+63D61D-60F
I was planning to use some combination of INDEX,MATCH and/or the SUMPRODUCT function to complete this task.
In the past I've used the SUMPRODUCT function creatively { ex. =SUMPRODUCT(--(repname=$C$2),--(question=C5),--(test=$E$3)) }
However, since the criteria I need to consider are not organized in individual records, the SUMPRODUCT function did not work.
See the attached work book for details on the test grade data.
Test Data Workbook.xlsx
The workbook also includes a mock-up of the summary and detail I'm trying to create.
My specific questions are on the "Questions" tab.
Writing a single nested IF statement function to calculate the grades of the students the following rules:
If the student grade isHis/her letter grade is
Greater or equal to 90:A
Greater or equal to 80 and less then 90:B
Greater or equal to 70 and less then 80:C
Greater or equal to 60 and less then 70:D
Any other grade:F
I am creating for the pupils at my school. Comparing the grades they were predicted and have achieved I would like to give them a score.
I have already achieved this but it means wasting 3 columns for each subject to work it out. The ambition is to do all the working out and assign a point score value in 1 column.
The spreadsheet it attached if anyone would be kind enough to have a look and see if there may be a formula which will do what I need.
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.
i have two columns...a and b (a w/ names, and b w/grades). then i have the table lookup with names and grades all mixed up for many rows. i want to be able to average the grades with appropriate names.
=average(vlookup(name, table, column, false))?? i don't get it to work and how can i specify the grade to average?
I am trying to sum up a list of cells that have formulas attached so if there is no number in the formula it shows up as #N/A how do I make it so wherever there is an #N/A it equals 0?
View 9 Replies View RelatedThe formula is designed to sum a set of data based on 22 variables between a certain date range. In order to keep the formula manageable, I have grouped the variables into arrays.
EX.
Array 1. - 20 potential choices (Service codes)
Array 2. - 2 choices (pkg types)
Array 3. - 2 choices (volume type)
Array 4. - 3 choices (company names)
Each array (listed horizontally on one spreadsheet) calls a specific column of data to match from a different worksheet. I have no problem if only applying one array but multiple arrays return incorrect values.
I have a table that have a test or character and value (number) like
A 5
B 6
C 3
And another table that has a set of characters that I want to sum the value of the characters like
A B C 14
So that the result of summing the characters will be in the next cell.
I am having real trouble with a formula.
I have used a similar formula for to calculate a column.
Can any one see where I am going wrong. It is the cell highlighted in yellow on the attached spreadsheet.
I'm having a problem summing after running arrays to analyse data. I have a list of four possible outcomes and I need sum up the totals, but sometimes there are no results, so there is #DIV/0 in the list I need to find the total for.
is there some code or some kind of formatting that would give me a total even if #DIV/0 is in the list?
Is there a easy way to sum a column of numbers in excel and ignore hidden rows?
View 9 Replies View RelatedI'm working with a worksheet with 4000+ rows, so I'm looking for a formula to automate a summation. In Worksheet1 (4000+ rows) I have data from 1998 to 2008 for each month and day:
Year Mo Dy Data
1998 1 1 41
1998 1 2 35
1998 1 3 79
...
2008 12 29 39
2008 12 30 81
2008 12 31 42
In Worksheet2 (365 rows) I have data for just months and days:
Mo Dy Sum
1 1 94
1 2 103
1 3 222
...
12 29 65
12 30 211
12 31 98
I'd like to take each Jan 1 datum for each year from Worksheet1, sum them, and place the value in the cell that corresponds to Jan 1 on Worksheet2. I've primarily tried using the VLOOKUP function, but can't seem to make it work.
I would like to get the sum of a range in VBA and tried the following that did not work:
S= Sum(Range(Cells(1, 1), Cells(1, 6)))