Troubleshooting Course Problems
Excel has a number of tools that can be used to spot students who may be having problems with the course or assignments which may not been as effective as originally intended.
This Page
- Finding Students with Low Percentages
- Finding an Exam Mean and Standard Deviation
- Finding Grade Distribution
- Other Measures
Finding Students with Low Percentages
If you are able to maintain a running grade, then students who are not doing as well can be identified for further tutoring or counseling.
Sorting Gradebook by Grades
You can sort a gradebook so that the students with the lowest overall percentages are sorted to the top.
- Highlight the column containing a running grade or major assignment grade.
- Type Control+A (Win) or Command+A (Mac) to select all the contents of the file.
- Click the A-Z Sort Ascending icon. Grades will be sorted by percentage.
- To re-sort the data, highlight the names column, then follow steps #3-4.
Using Conditional Formatting to Find Low Scores
- Move your cursor to any cell containing a grade.
- In the menu, go to Format » Conditional Formatting.
- In the Conditional Formatting window, change the settings to
- Cell Value Is
- Less Than
- Enter a minimum percentage (e.g. 70).
- Click the Format button and select a text color such as red or green. Click OK to close.
- To copy the formatting to other cells, select the Format Painter (paintbrush) icon.
- Highlight all the cells in the column to spread the formula.
- Cells where percentage is less than 70% will change color.
Finding an Exam Mean and Standard Deviation
Average or Mean
To find the average score for an exam:
- Move your cursor below the last exam score.
- Enter the AVERAGE formula with the range of cells in the column containing exam scores. (e.g. =AVERAGE(N2:N5))
- Note that an average can be taken for a raw point score or for a percentage.
- To spread the formula to other cells in a row, highlight the first cell and other cells to the right in that row, then choose Edit » Fill » Right (or press Control+R on both Win/Mac).
| A | L | M | N | P | |
|---|---|---|---|---|---|
| 1 | Name | All Problem Sets | %age | Midterm | Final |
| 2 | Picard, J. | 430 | 95.56% | 90 | 138 |
| 3 | Riker, W. | 415 | 92.22% | 100 | 140 |
| 4 | Troi, D. | 438 | 97.33% | 93.5 | 148 |
| 5 | Zellig,W. | 365 | 81.11% | 77.5 | 110 |
| 6 | Average | =AVERAGE(L2:L5) | =AVERAGE(M2:M5) | =AVERAGE(N2:N5) | =AVERAGE(P2:P5) |
Standard Deviation
To find the standard deviation, use the formula =STDEV(L2:L5)
Interpretation
- If the exam grades are in a "normal distribution", about 2/3 of the students will have an score within one standard deviation of the mean. About 1/3 will have scores between the mean plus the standard deviation, and 1/3 will have sores between minus the standard deviation.
- A smaller standard deviation means more students clustered about the mean. A large standard distribution means a wide variation in performance and less precision in the data.
- A smaller standard deviation also means that the assignment or exam is probably a more accurate assessment of student performance.
Finding a Letter Grade Distribution
Use the COUNTIF(A1:A2,"grade") Formula to find how many A's, B's and other grades were earned.
- To find one grade (e.g. "A"), use COUNTIF to find how many A's are within a range. For the mock spreadsheet below, you would use the formula
=COUNTIF( K2:K5,"A"). Note the quotes around the letter grade in the formula. - To get a range of "A-B", enter
=COUNTIF(K2:K5,"A")+COUNTIF(K2:K5,"B")
| A | H | I | J | K | |
|---|---|---|---|---|---|
| 1 | Name | Totals | Percent | Intermediate | Final Letter Grade |
| 2 | Picard, J. | 112 | 93.3% | A- | A- |
| 3 | Riker, W. | 106 | 88.3% | B+ | B+ |
| 4 | Troi, D. | 117 | 97.5% | A | A |
| 5 | Zellig,W. | 83 | 69.2% | D |
To find how many instances of each letter grade there are:
- Below or to the right of the grades, insert "A","A-" through "F" into separate cells.
- Enter the COUNTIF formula in the cell next to "A" and refer to the cell value in the formula. For instance if "A" is in cell L1, then the formula in column M would be:
=COUNTIF(K2:K5,L2) - For each COUNTIF formula, the range remains the same, so use the $ symbol to fix the range for all formulas. (e.g. $K$2:$K$5)
- Now the formula can be cut to other cells without affecting the range.
See mock spreadsheet below.
| A | H | I | J | K | L | M | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Totals | Percent | Intermediate | Final Letter Grade | Possible Letter Grades | Count |
| 2 | Picard, J. | 112 | 93.3% | A- | A- | A | =COUNTIF($K$2:$K$5,L2) |
| 3 | Riker, W. | 106 | 88.3% | B+ | B+ | A- | =COUNTIF($K$2:$K$5,L3) |
| 4 | Troi, D. | 117 | 97.5% | A | A | B+ | =COUNTIF($K$2:$K$5,L4) |
| 5 | Zellig,W. | 83 | 69.2% | D | B- | =COUNTIF($K$2:$K$5,L5) |
Other Measures
Median
The mean measures the halfway point in scores. Half the students scores will be above the mean and half below. If the median differs widely from the mean, then scores are skewed from the normal distribution towards the median.
You may have a large group students performing well or a large group performing not so well.
Formula: =MEDIAN(L2:L5)
Correlation
If you are concerned that assignments are consistently assessing student performance, then you can use the Correlation formula to ensure that students who did well on one assignment, do equally well on another.
Note: This formula compares cores between two ranges.
Formula: =CORREL(L2:L5, M2:M5)
Interpretation
The result of this formula is a number between -1.0 to 1.0. The higher the number, the more consistent the exams are. A number above .5 may be considered acceptable, but higher numbers are better.
For the data set given in the mock spreadsheet below, the correlation between the two exams is .76, which means the exams were fairly consistent.
| A | L | M | N | |
|---|---|---|---|---|
| 1 | Name | All Problem Sets | Midterm | Final |
| 2 | Picard, J. | 430 | 90 | 138 |
| 3 | Riker, W. | 415 | 100 | 140 |
| 4 | Troi, D. | 438 | 93.5 | 148 |
| 5 | Zellig,W. | 365 | 77.5 | 110 |
| 6 | Average | =AVERAGE(L2:L5) | =AVERAGE(M2:M5) | =AVERAGE(N2:N5) |
| 7 | Standard Dev | =STDEV(L2:L5) | =STDEV(M2:M5) | =STDEV(N2:N5) |
| 8 | Median | =MEDIAN(L2:L5) | =MEDIAN(M2:M5) | =MEDIAN(N2:N5) |
| 9 | Exam Correl | =CORREL(M2:M5,N2:N5) =.76 |
Last Update: May 8, 2006