Clean-up details from last week: demonstrate how to reposition X axis tic marks and labels to bottom of graph.  Click the Y axis - select the Scale menu.  Look for "Crosses (X) axis at"- select a number other than 0 and see what happens to the X axis tic marks and labels.

Begin by downloading the file C4T05.txt into your own folder, flash drive or desktop.   Right click on a link and select "save target as" - that's one way to do it.  Left click and you will see the text file - final grades for a class titled Computers for Toons. 

Save as text file, not html!  Begin by clicking on this link

C4T05.txt

Text or ASCII (.txt, .dat, .prn) files provide a means to pass data between different programs that might not recognize file formats used to store program files. Excel recognizes ASCII files and attempts to parse them (isolate related sets into columns).

Watch and note how Dr. D. manages the sorting of elements of this file into columns of a worksheet.

Data - Sort: Alphabetize C4T05.txt; next, sort according to increasing student grade; then, by decreasing student grade. Organize these, one to a worksheet. You may need to Copy - Paste some ranges of cells from one worksheet to another.  Rename worksheets to better reflect their content.

Grade distribution: plot both a histogram and a pie chart showing the grade distribution for this class. This used to be a simple function (Analysis was part of the Data menu on former editions of Excel). Things have changed.

The new EXCEL has managed to make distributional analysis very difficult for those without this Secret Decoding Ring.

Amaze your friends! Learn how to perform an array operation.

We are going to use the Frequency function, with will count the number of students in each grade range.  

Set up a bin range - a range of values within which to place numbers telling you how many from the sample fall within each range. Note: each bin will count values greater than the previous value of the bin list, up to and including the number for that bin. Always arrange your bin in a column, with values increasing top to bottom.

Now, highlight the cells in the column adjacent to your bin range, adding one additional cell at the bottom to hold any values greater than the highest value in your bin range.

Click the Insert Function button (f_x). Under Statistical, find and click FREQUENCY. Frequency requires 2 arguments: the range of values you wish to count into bins, and the bin range. Click on the right end of the "values" argument space, highlight the appropriate range for values, click the right box, then do the same for "bin" argument box. Then hit OK. A number will appear in the first cell of the output range.

Secret Decoding Ring: Before doing anything else on the worksheet, hit key F2. Then hit "Ctrl-Shift-Enter" in that order, keeping both Ctrl and Shift pressed until you hit Enter. Release all at once. The rest of your output range is now filled - just like magic!

Display results of the Frequency table in both pie chart and histogram on a new worksheet.  Add some labels to use in your pie chart and histogram (grades: F, D, C, B, A). Make both a pie chart and a histogram showing grade distribution in Computers for Toons.

Note that changing a grade results in an updated graph.  Try it!

Afterthoughts: Other useful array functions include matrix multiplication and matrix inversion.  Both ecologists and geologists in the graduate program have used these to solve problems associated with data characterization encountered in conducting their thesis research.  Back when I was an undergraduate, we worked with the theory of matrices but never used them on anything practical because of the vast amount of calculations required.

Label your charts and attach your spreadsheet to e-mail, sending it to Dr.D by Noon, Monday, February 1. 

Next week: Introduction to Surfer.  This is entirely new to many of you so please be punctual!

Return to Syllabus