PSU Anaheim 2013

Test Scores

We didn't get a chance to go over this exercise in detail, because I couldn't remember where I put the Test Scores file.

Download it from here.

In brief, the only function I use here to break out the pieces of the test score is the MID function. The premises are as follows:
  1. I get a test results data file, either from the state, or a testing company (ETS, the College Board, someone else)
  2. The test results are all run together in a single line, with no discernible delimiter (Smith John 05101999 )
  3. I have a "crosswalk" from the testing company, which is a term to describe the layout of the data (the first five characters are the last name, the sixth through the twelfth character is the first name, that kind of stuff)
  4. I want to break it out into its component pieces
The key is to use the MID function to say, "extract the data from A2 of the Raw Data sheet. Start at the number character listed in A2, and grab the number of characters in A3."

You'll see that the MID functions use absolute references, both for columns and rows. The Raw Data cell being referenced has an absolute reference next to the column, so that I can copy the formula across columns and still refer to the A column. When referencing what cells contain the number of characters to extract, the absolute reference is next to the row, so that as I copy the formula down the rows, the reference to those rows stays consistent.