Posted: 7/7/2010 3:04:47 PM EDT
|
Hey folks. I'm currently doing grades in excel. It is set up to sum up the various assignments and give me a percentage grade. I would also like to do a "grade report" with the following. 1) The numbers of students who got A's, B's, C's, D's, and F's. 2) Shows the highest grade 3) Shows the lowest grade. I am excel illiterate. Please let me know if this is possible, and if so, how to do it. I was at least able to figure out how to do a class average.  |
|
Have you assigned a letter grade (a) already or are you counting grades by numerical range (b)?
1a. =COUNTIF(cell range,"A"): count the number of A's 1b. =COUNTIF(cell range,">95")-COUNTIF(cell range,"<=100"): count the number of grades greater than 95 and less than or equal to 100 2. =MAX(cell range): maximum value 3. =MIN(cell range): minimum value ETA: cell range would be the cells that contain the data you want to evaluate. For example, B2:B20. |
|
Presume that you have 50 students, and grades are in columns A through X. Total scores (values between 0-100, not percentages) are in Column Y. 1) Make a lookup table that has the following in two columns: 0 F 60 D 70 C 80 B 90 A We'll say that this table lives in the range AA1 - AB-5 In another column, say at the end of the row where you've compiled all of the scores, you'll need to use the VLOOKUP command: =VLOOKUP(M1,$AA$1:$AB$5,2) This will report "A" for any score 90 or above, "B" for any score 80-89.999, and so on. At the bottom of that column, in another set of cells, you could have this: =COUNTIF(Z1:Z50,"A") =COUNTIF(Z1:Z50,"B") =COUNTIF(Z1:Z50,"C") =COUNTIF(Z1:Z50,"D") =COUNTIF(Z1:Z50,"F") 2) =MAXIMUM(Y1:Y50) 3) =MINIMUM(Y1:Y50) |
|
1) The numbers of students who got A's, B's, C's, D's, and F's. 2) Shows the highest grade 3) Shows the lowest grade. 2) use =MAX(Cell1:Cell100) where Cell1 to Cell100 is the range of cells you want to find the max value of. 3) same as 2, but its MIN() instead of MAX() |