Using the Data Analysis tool in Excel to Analyze Infinite Campus Data

The following directions will show you how to export your grade book data to Excel and then show you how to analyze that data.

Directions:

  1. In campus click on and expand “Reports”.
  2. Then click on “Grade Book Export”
  3. Under the “File Format” drop down menu select “Comma Separated (recommended)”
  4. Choose the assignments and students you wish to export
  5. When analyzing test data, I would recommend you just export that assignment
  6. For this demo, choose one assignment that you would like to look with more depth.
  7. At the bottom of the window click on the “Generate Report” box
  8. In the “File Download” dialog box click on the “Save” button.
  9. Save the file to where ever you save your work
  10. You now have your grade book data in a program that allows you many ways to analyze it
  11. Open the program “Excel”
  12. Click on “File” and then “Open” and navigate to where you saved you grade book export

a.  The file may not appear to be where you saved it, if this is the case in the open dialog box change the “Files of type” drop down menu to “All files (*,*)

  1. Now you should see your students names and grades on the assignment you choose
  2. If you double click on the lines between the columns excel will widen them so you can see all of the names and assignment name
  3. Since most of use deal with assignment data in percentages, we need to change the assignment from its point value to its percentage.
  4. To do this in cell C3 type in the following formula “=(B3/# points the assignment is worth*100)”
  5. For example “=(B3/161*100)
  6. Now click on cell C3 and then click and hold down on the lower right hand corner of the cell and drag down to last student in your list
  7. Excel will put in the formula into each cell for you when you do this
  8. Now you are ready to analyze some data!!!
  9. The first time you do this you will have to add on the data analysis tools
  10. To do this click on “Tools” and then select “Add-Ins” and then in the dialog box that appears check the “Analysis Toolpak” and “Analysis – VBA” boxes and then click “Ok”
  11. Now to calculate descriptive statistics for your data click on “Tools” and select “Data Analysis”
  12. In the “Data Analysis” dialog box choose “Descriptive Statistics”
  13. Click on this icon at the end of the “Input Range:” entry box
  14. Now select that percentage data you created earlier
  15. Under “Output” options select “Output Range” and click on the same button as you did for the input range and select a cell that is a bit away from your data.
  16. Check the box next to “Summary Statistics”
  17. Your “Descriptive Statistics” dialog box should look something like this now
  18. Click “Ok” and the summary statistics for you assignment will appear in the cell you choose as your output range
  19. This data includes the mean, median, mode, standard deviation, range, minimum and maximum values
  20. All of these are useful and can tell you about your students performance on the assignment
  1. The descriptive statistics are helpful, but at times can be misleading. To gain a better understanding of your student’s performance on the assignment you will now be creating a histogram to show the number of students that scored at each grade level.
  2. Before we start on the histogram we have to place a few things into Excel
  3. In Column G place the following
  4. This will be used by excel to sort your assignment data by grades
  5. Once again click on “Tools” and select “Data Analysis”
  6. This time choose “Histogram” from the “Data Analysis” dialog box
  7. Select the input range the same way you did in step 19
  8. For the “Bin Range:” select the data you entered in Column G
  9. Just select the cells that contain data, no empty cells
  10. Select a cell for the output range to be placed and check the box next to “Chart” output
  11. Then click ok
  12. The chart and table that are now created show how many students scored A’s, B’s, C’s, D’s and F’s
  13. A = 100 , B = 89, C = 79, D = 69 and F = 59
  14. This type of data analysis is very helpful when comparing pre and post tests, it shows you more than just the average of both