Using Excel to Analyze OSAT Results

1
Using Excel to Analyze OSAT Results
2
Workshop Agenda

• Cutting and pasting data from the ODEs website
  into Excel
• Adding calculations (formulae) to Excel
  spreadsheets
• Copying formulae
• Downloading student results from secure Internet
  site
• Performing calculations on those results

3
Agenda (cont.)

• Creating new worksheets in Excel workbook
• Well create a template for disaggregated groups
• Using filters to view subgroups of your choosing
• Copying subgroups to separate pages

4
Copying Results from ODE Site

• Go to http//www.ode.state.or.us
• Point at Most Requested Items and a drop down
  menu appears
• Click on Assessment Scores 2003
• Pick year youd like to download using View
  Previous Years Test Results menu
• Click Search Test Results

5
Copying Results from ODE Site (cont.)

• Use scroll bar to scroll down to the district of
  your choosing
• Click on the district name
• Click District Report
• Click School Name or District Summary
• Use drop down menus to select the type of report
  and subject of test you wish

6
Copying Results from ODE Site (cont.)

• Minimize Internet Explorer (IE) Screen by
  clicking on leftmost of three buttons in upper
  right of screen
• Open Excel sheet into which youd like to copy
  the data
• Click on the IE logo on the task bar to expand it
  onto your screen
• Highlight the data you want to copy by dragging
  cursor across that data
• Edit / Copy
• Minimize the IE screen again

7
Copying Results from ODE Site (cont.)

• Click on cell that you want to the upper left
  most cell of the data to be pasted (A2)
• Edit / Paste
• Add labels in the first row
• Ill demonstrate adding 2002 Youll add 2003
• Some Hints
• Arrange data on the spreadsheet the same as it is
  on the website
• When pasting multiple sets of similarly arranged
  data, separate them with the same number of blank
  rows every time

8
Lets do some math

• What change do you see?
• How can we make it more clear?
• Lets calculate the change from year to year
• Click the cell to the right of the top-most Grade
  Does Not Meet Percentage in the 2nd set of data
  (2002) well call this the target cell (F12)
• Well put the formula for change in Percentage
  Not Meeting in this cell

9
Entering Formulae

• This formula will not use any built in functions
  Well simply subtract last years percentage
  from this years percentage to get the change in
  percentage
• Click on the sign to the left of the formula
  bar a formula tool appears
• Click on the cell with the percentage for this
  year just to the left of where your target cell
  is the cells reference (E12) appears in the
  formula
• Type a into your formula to indicate you want
  to subtract
• Click on the cell to subtract (E3)
• Check the Formula result in the tool to assure
  its working
• Click on OK The formula tool disappears and the
  result appears in your target cell

10
Now youre gonna do it

• Youll calculate the States change in the
  percentage of ? graders not meeting for the two
  years Your target cell will be just to the
  right of the States percentage not passing (L12)
• Click on your target cell
• Click on the sign to the left of the formula
  bar a formula tool appears
• Click on the cell with the percentage for this
  year just to the left of where your target cell
  is the cells reference (K12) appears in the
  formula
• Type a into your formula to indicate you want
  to subtract
• Click on the cell to subtract (K3)
• Check the Formula result in the tool to assure
  its working
• Click on OK The formula tool disappears and the
  result appears in your target cell

11
Lets Talk about Relational Formulae

• Click on the first formula we entered
• In the cell you see the result and in the cell
  editing window above you see the formula
• The computer doesnt remember the cells in the
  formulae it remembers their relationship to the
  target cell
• It remembers take the cell one to the left and
  subtract from it the cell one to the left and 9
  up
• We can copy this formula to any cell where the
  relationships are the same

12
Where Can We Use the Formula?

• Will this formula work to the right of the Meets
  Standard?
• How about all the other changes?
• We need to talk about copying formulae
• There are ways to copy one formula to multiple
  places
• There are ways to copy a single cell to a range
  under it or to the right of it
• There are ways to copy multiple cells to others
  in the same configuration

13
Copying a Formula to Other Cells

• Copy a single cell into another cell
• Select the cell to copy by clicking on it
• Edit / Copy
• Select cell into which you want the formula
  pasted
• Edit / Paste
• Note The cell can be copied multiple times
  without Edit / Copying again

14
Copying a Formula to Other Cells (cont.)

• Copying cells to cells beneath (or to the right
  of) it
• Select the cell to be copied and, continuing to
  hold the mouse button down, drag the selection
  down through the range to be filled with the same
  formula
• Edit / Fill / Down
• To Copy a cell to the right select the cell and
  the range to the right to be filled, then Edit /
  Fill / Right

15
Copying a Formula to Other Cells (cont.)

• Copying a range of cells into an analogous range
  of cells
• Select first cell to be copied
• Drag the cursor over the range to be copied
• Edit / Copy
• Select top-left cell analogous to the set of
  cells selected
• Edit / Paste

16
Lets Review

• You can cut and paste tables of data from the
  internet onto a spreadsheet
• You can perform calculations on the numbers
  derived from the internet
• You dont have to re-enter a formula if it is the
  same (relationally) as those needed in other
  cells
• You can copy single cells (formulae) or ranges of
  cells into new places and the same calculation
  will be done (relationally) as in the original
  cell

17
Saving your Excel worksheet

• Click on File / Save
• Find the folder into which you want to save the
  worksheet or create a new one
• Lets create one on your desktop by
• Clicking Desktop icon to move to the desktop
• Clicking on New Folder icon
• Naming the folder (WCC Workshop)
• Now provide a name for the worksheet (School
  Reading)
• Click Save

18
Lets Practice

• Take five minutes to practice
• building simple formulae
• Copying formulae singly, with the fill feature,
  and in groups
• Using the fill tool to create sequenced data
• When we start again, well get more complete data
  from the internet and learn to disaggregate
  sub-groups
• Well learn to do more complex math on those
  groups

19
Getting the OSAT Data

• Open Internet Explorer
• Type http//www.eddataonline.com/oregon/pass.htm
  onto the address line and click Go
• Enter District ID
• Enter Password
• Click Login
• Click Student Test Results 2002-2003 School Year

20
Lets Get the Data (cont.)

• Select the Reading test at the bottom of the list
  (reconciled results for entire year) and press GO
• On File Download dialog press Save
• Click Desktop on the left of the Save As dialog
• Click on your Training Materials folder
• Click Save on the bottom right of dialog to save
  the file in that folder

21
Lets Get the Data (cont.)

• Scroll down on the same page to find documents
  describing field headings for the files just
  downloaded
• Click Word 97 logo next to the one you want
• On File Download dialog click Save
• Save this in your Training Materials folder too
• Close your Internet Explorer
• Open the Training Materials folder and double
  click on the Word document you downloaded (or
  mine)
• Then minimize that window and double click on the
  Excel file you downloaded (or mine)

22
Structure of the Data File

• Terminology
• Fields and Records
• Field Headings
• Adjust column width
• Select the column A
• Use scroll bar to move to far right row
• Shift / Click on last columns letters
• Point at any of the separators between columns

23
Structure of the Data File (Cont.)

• Save As an Excel Workbook to preserve formatting
  and formulae
• Click on File / Save
• Navigate to desktop / WCC Workshop folder
• Set Save as type to Microsoft Excel Workbook
  (.xls)
• Click Save
• Look at cryptic field headings on Excel file
• Look them up in Word file
• Lets look at whats here (and whats not)

24
Overview of What Youll Learn

• Adding useful analysis formulae to calculate the
  performance of the group(s)
• Creating a template sheet with no data but with
  all the calculations
• Using the template to create as many sheets onto
  which the groups can be copied as you wish
• Using Auto Filter to isolate the group you want
  to study
• Copying subgroups onto your blank calculation
  pages and renaming them appropriately

25
Adding calculations

• Most common calculations
• Averaging RIT scores
• Counting Not Mets, Mets, and Exceededs
• Calculating Percentage Not Met, Met, Exceeded
• Lets create some space for calculations
• Highlight rows 1 through 9 by clicking on the 1
  and dragging down to 9 and releasing
• Click on Insert / Rows
• Why create space at the top?

26
Averaging groups RIT scores

• Averaging
• Find TOTSCALE on Subgroup sheet
• Click Row 9 above the heading
• Insert / Function / Statistical / Average
• or
• Use Formula Tool to enter formula
• Select data to be averaged
• Some tricks to find the bottom
• Click top cell use scroll bar to find the
  bottom shift / click
• Click top two or three cells edit row number in
  formula

27
Calculating Numbers of Students Did Not Meet,
Met, Exceeded

• Counting Did Not Meet, Met, Exceeded
• Find TOTPER_G column
• Click on Row 9 above field heading
• Insert / Function / Statistical / CountIf or use
  Formula Tool to create formula
• Select Data to look in for Range
• Enter D in Criteria
• Click OK
• Do same process with M and E in two cells above
• Label it all.

28
Calculating Percentage of StudentsDid Not Meet /
Met / Exceeded

• Calculating of Didnt Meet, Met, Exceeded
• Label 3 cells above as E, M, NM
• Enter formula for calculating of Exceeded
• Type equal sign
• Click number of exceeded
• Type divided by sign /
• Insert / Function / MathTrig / Sum or Formula
  Tool
• Select all three numbers to get total number
• Type enter ?.
• Follow same procedure to calculate M and NM

29
Copying Calculations

• Would these formulae work (relationally) above
  the subtest results?
• You betcha
• Copy range of calculations
• Determine top left of place to paste over subtest
• Paste range of calculations there
• Do this for all subtests

30
Creating a template page for subgroup analysis

• Right Click on tab for this sheet (Sheet1)
• Left Click on Rename
• Name this sheet District Data (Enter)
• Now right click on that tab again
• Left click on Move or Copy
• Click Save a copy box
• Click OK
• Now highlight all the student data / Edit / Clear
  / All
• Click on tab for District Data (2)
• Click Rename
• Name it Subgroup Template (Enter)

31
Creating Subgroup Pages

• Rearrange the order of the sheet tabs
• Click and drag the template sheet to the right
• Now right click on template tab
• Left click on Move or Copy
• Click on Sheet 2 so the new copy will go before
  that sheet
• Click Create a copy
• Click OK
• Do this as many times as you have subgroups

32
Lets Review

• You can create space for calculations by
  inserting rows
• You can have the computer average, count, and
  find percentages using built-in formulae
• You can create a blank template page containing
  all these calculations
• You can make as many copies of the blank page as
  you want
• Now lets separate out some subgroups and put
  them on these pages

33
Separating Groups

• Click on the row number next to the column
  headings to select the row
• Click on Data in the menu bar
• Point at Filters in the drop down menu the then
  click on Auto Filter in the 2nd menu
• The heading row will have changed so that the
  headings are now drop down menus

34
Separating Groups (cont.)

• Click on the triangle to see the contents of that
  column click on the content youd like to
  isolate
• All records that do not contain that content are
  hidden
• The other records are still there only hidden
• We must copy these unhidden records to another
  location to do calculations on the subgroup

35
Separating Groups (Cont.)

• Select the entire group youve separated
• Select the topmost row click on its number
• Scroll down to last row of group
• Shift / Click on number of last row
• Click on Edit / Copy
• Click on tab of first open copy of the template
• Click on top left cell under headings (A11)
• Click Edit / Paste
• Rename to page to reflect the sub-group
• Go look at the results

36
Lets Review

• Use Data / Filters / Auto Filter to turn on
  filtering
• Select contents to preserve by using drop down
  menus on headings
• Select the unhidden group
• Copy that group to a blank template page
• Rename it
• Disaggregate as many groups as you want

37
Saving and Cleaning Up

• Click File / Save
• Close Excel (X in upper right corner)
• Double Click on both the WCC Workshop and
  Training Materials folders
• Drag the WCC Workshop.ppt file from the Training
  to the Workshop folder
• Close both folders by clicking on the X

38
Saving and Cleaning Up (cont.)

• Right Click on WCC Workshop folder
• Left click on Properties
• Check Size on Disk
• If its bigger than 1.44 Mb youll have to delete
• Close that dialog by clicking OK
• Put a floppy disk in the disk drive
• Double click on My Computer
• Drag WCC Workshop folder onto 3½ Floppy icon
• Drag Training Materials folder to the trash

39
Whew!

• Use the Slide Show in your Packet and the File
  youve just created to do this at home with your
  data