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.
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.
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 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 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 write a formula that would assign a letter grade (in row W) to the average in row V, based on the school's scale. So, row V contains my averages (in % form) and I want row W to have the letter equivalents.
93+=A
85-92=B
75-84=C
67-74=D
<66=F
When I type a single lower case letter into a cell, what formula or conditional formatting should I use to always convert it to a capital letter automatically?
View 9 Replies View RelatedFor the below formula is it possible to replace the B's (column location) with a cell Say Z146 which contains the letter B (or a number if thats easier and someone can tell me the numbers for each column).
When the formula is dragged into the next cell (down) it takes its column reference from Z147 and then my life becomes so much easier.
=IF(INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0))"",INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0)),"")
I need to change a few hundred cells (one column) where the first letter may be a capital letter to a lowercase letter.
So:
GetAwardfromBid to getAwardFromBid
or
SmallLertter to smallLetter
I 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 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 Replies View RelatedI 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 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
Need 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
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?
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 want to apply Data Validation to a cell, so that only the following combination of letters and numbers can be entered.
Letter Letter Number Number Number Number Number Number Letter.
e.g AB123456C.
E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).
E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.
I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.
TotHCInv.Value = WorksheetFunction. Sum(KRInv, PBLInv, CRInv, PVInv)
If i >= 34 Then CPSCtphRMA.Value = WorksheetFunction.Average("G" & (i - 30) & ":G" & i)
The first line runs properly, but the second line bugs out with the error message "Unable to get the Average property of the WorksheetFunction class". I can simply do the math, but I thought that using the worksheet function would be easier than summing and dividing. I'm curious, though, as to why I can't seem to use the Average function.
Can I get a minimum average and a maximum average, I have a worksheet with days of supply for 100 stores with about 100-200 products each, the dos resides in column D.
I was going to create a summary page and reference column d.
The following just gives me the min, I want the min average if possible: