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 have 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 group of cells corresponding to student names, assignments, assignment score cumulative percentage/score. DO NOT INCLUDE any assignment averages, assignment totals or arrays
Open the Data sorting window.
Office 2007 (Win): Click the Data tab, then click the Sort icon towards the middle. A pop-up window opens.
Macintosh/Pre Office 2007 (Win): Go to theData menu and select Sort. A pop-up window opens.- In the Sort By menu, select the column with the cumulative score.
- In the Order (Office 2007) or Then By (Mac) menu, check Ascending. Click OK to continue. The rows will be reordered.
- To return the rows to the original order, either perform an Undo (Control+Z on Windows/Command+ Z on a Mac), or follow Steps #1-4 and resort by student name in ascending order.
Using Conditional Formatting to Find Low Scores
Conditional formatting allows you to change the formatting of a cell (e.g. background color or text color) based on a formula. The examples below describes how to highlight cells where the total percentage is below 70%.
Office 2007 (Win)
- Highlight cells containing the grades you wish to format (e.g. a column with an exam grade or final grade).
- On the Home tab in the Styles group, select the Conditional Formatting menu.
- In the Conditional Formatting menu, select the option for Highlight Cells Rule then Less Than. A new window will open.
- Enter a minimum percentage or total in the box on left, then select a formatting option in menu on the right. Click OK to close.
- Cells where percentage is less than 70% will change color.
- To copy the formatting to other cells, use the Format Painter (paintbrush) tool.
- To edit a conditional format, follow steps #2-3 and change the values in the pop-window.
- To remove a conditional format, go to the Conditional Formatting menu (Step #2) and select Clear Rules.
Macintosh/Pre Office 2007 (Win)
- Highlight cells containing the grades you wish to format (e.g. a column with an exam grade or final 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).
- Cells where percentage is less than 70% will change color.
- Click the Format button and select a text color such as red or green. Click OK to close.
- To edit a conditional format, follow steps #2-3 and change the values in the pop-windows.
- To delete a conditional format, follow step #2-4 to get to the formatting window. Click the Clear button. The Conditional Formatting window should read No Format Set.
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 press Control+R (Win/Mac).
Note: The Fill Right function is also available on the toolbar or within the menu.
| 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 a 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 scores between the mean plus or 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 of 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 17, 2011