Step 1: Setting up the convergent data spreadsheet in excel

Step 2: Download the 1.1.3 report to excel

Step 3:  Formatting the 1.1.3 individual student list by grade report in excel

Step 4: Adding columns of information to the spreadsheet

Step 5: Filtering and adding colors to signify proficiency

Step 6: Determine the number of 4th grade students proficient at the building level

Step 7: 4th Grade proficiency at the teacher level

Step 8: 5th Grade mastery at the teacher level

Step 9: Prediction validity

Step 10: Answer the three data questions relative to and across each assessment


Step 1: Setting up a convergent data spreadsheet in excel

Learning goal

Know and understand the steps, and use of the Student Convergent Data Spreadsheet across multiple assessments and its usefulness in monitoring teacher effectiveness and thus student outcomes across the school year.

Success criteria

  1. I can download EdInsight report 1.1.3 and use it as base for the convergent data spreadsheet.
  2. I can copy, paste, and add columns of essential information in order to track teacher effectiveness and student progress across a variety of interim assessments.
  3. I can add filters in the convergent data spreadsheet to sort and maintain the document’s integrity.
  4. I can calculate the percent, and number of students who are proficient or achieve mastery on the summative and interim assessments, respectively by teachers and at the building level.
  5. I can use the above information to more effectively collaborate in Data Team meetings or PLCs.
  6. I can use this data to help focus the conversations in Data Team meetings or PLCs
    • What does the data tell us?
    • What are we going to do about it?
    • Is it working?

Notes:

  • You always want to keep the original EdInsight 1.1.3 report in its intact state in case you have to refer to it at a later time.
  • To generate the Convergent Data Spreadsheet you will copy and paste certain columns of the EdInsight 1.1.3 report into the next sheet on the Excel document.

Task: EdInsight 1.1.3 (Use Firefox)

Generate the EdInsight Report “ASMT 1.1.3 Individual Student List by Grade” in EdInsight for a given building at a specific grade in a given content area.

Detailed Steps for Setting up the Convergent Data Spreadsheet (to complete the EdInsight 1.1.3 Task):

  1. Log into EdInsight:
  2. Click on “EdInsight Reports”
  3. Click on the “1.0 ASMT Assessment / Growth”
  4. Click on the “ASMT 1.1 Individual Student Reports”
  5. Click on the “ASMT 1.1.3 Individual Student List by Grade” report
    • This report displays a list of the individual students’ National Percentile Ranks and National Standard Scores on the Iowa Assessments [formerly known as the Iowa Test of Basic Skills (ITBS) or Iowa Test of Educational Development (ITED)] for the specified year, district and/or building, and (optionally) grade level.
  6. Select each of the options starting with the school year:
    • Select the most current school year
    • Select an AEA (optional)
    • Select a Testing District
    • Select a Testing Building:  Choose an elementary building
    • Select a Grade Level:  Choose any grade level available in that building
    • Select a Subtest:  Select Mathematics or MT No Comp (a)
  7. Click Finish when done. This generates the 1.1.3 report.

Once the report is finished running it will appear on your screen.


Step 2: Download the 1.1.3 report to excel

Detailed steps:

  1. Click on the icon in the right hand upper corner that looks like this: Picture of the icon located to the left of the “Add this report” icon (located to the left of the “Add this report” icon).
  2. A drop down menu will appear that has the following options:
    • View in HTML Format
    • View in PDF Format
    • View in XML Format
    • View in Excel Options
      • Click the “View in Excel Options” and the following options will appear:
        • View in Excel 2007 Format
        • View in Excel 2002 Format
        • View in Excel 2000 Single Sheet Format
        • View in CSV format
      • Click on the option that matches your computer — Excel 2007 is the most used option
        • Open the file with Microsoft Excel
        • Click “Ok”

This will download the “ASMT 1.1.3 Individual Student List by Grade” to an Excel document. Notice that Excel has numerous sheets available in all Excel documents.


Step 3: Formatting the 1.1.3 individual student list by grade report in excel

Learning Goal

Have a good understanding of the usefulness and power of Excel as a tool to help display, sort, graph, and analyze student data at the district, building and teacher level.

Success Criteria

  1. I can copy, paste, and add columns of essential information to create an Excel Convergent Data Spreadsheet in order to track student’s convergent data across the year.
  2. I can add filters to the spreadsheet to sort and maintain the document’s integrity.
  3. I can add additional columns of information on students as needed.
  4. I can create the necessary formulas in order to calculate percent proficient or achieving mastery across various students’ assessment data.
  5. I can convert numbers to percentages in an Excel document.
  6. I can interpret the Convergent Data Spreadsheet and identify student(s) who are receiving instruction that is meeting their needs, and student who are receiving instruction that is not meeting their needs and take corrective action where needed.
  7. I can use the Convergent Data Spreadsheet as an additional tool to make sound instructional decisions based on student data.

Task

Copy and paste the following columns of information into the next tab in the Excel document. Start at the title of each cell.

  • Last Name
  • First Name
  • Grade
  • State ID Number
  • Gender
  • Race/Ethnicity
  • IEP
  • FAY
  • National Percentile Rank
  • National Standard Score
  • Achievement Level

Detailed Steps for “ASMT 1.1.3 Individual Student List by Grade” Report

  1. Place your cursor on the cell location B25 entitled “Last Name”
  2. Left click your mouse and highlight these columns “Last Name,” “First Name,” and “Grade”
  3. Highlight each student in the list.
  4. Right click your mouse and select “copy”
  5. Click the Tab entitled “Sheet 1” on the bottom of the document
  6. Move your mouse to location B3
  7. Right click your mouse and then left click on the first paste option that looks like a clipboard with a piece of paper on it. This will paste the information in the Excel sheet.
  8. Repeat this process for the remaining columns of information.

Step 4: Adding columns of information and color to the convergent data spreadsheet

Adding Student Information

We will add students’ assessment data in a number of columns across the school year. The number of columns depends on the number of assessments given, and the amount of other information added. Remember, add students’ assessment scores in the order that are given across the school year.

Don’t just assume that there is alignment of assessment content with these assessments across the school year. Verification that this alignment exists is a constant conversation educators should be having. This conversation will strengthen educators’ knowledge of the Iowa Core standards. If this alignment does exist, there should be no surprise by how students score on the summative assessment. In fact, you should be able to predict how students will do on the summative assessment by the negative and positive predictive power of these assessments to the summative assessment.

Most importantly, the Convergent Data Worksheet should clearly demonstrate whether the instruction and interventions provided to students are working as intended across the school year. This whole process is about how curriculum and assessment should directly influence INSTRUCTION.

Questions to Ask

Remember assessment scores of and themselves are never said to be valid. It is the inferences we draw from those assessment scores that are valid or not. This said, there a few things to consider about the quality of these assessments.

  • Do the questions on these assessments align well with the Iowa Core standards (Webb’s Depth of Knowledge) that are being taught?
  • What should the cut score for mastery be on these assessments (i.e., 71%, 80% or 85% correct)?
  • How many questions (12, 13, 14 or more) are necessary to adequately cover the content that is being assessed on these assessments?

Task:

  • Add “Teacher” in column A in the Excel sheet
  • Type in the teacher’s name on each row that corresponds with their students. Add new students as necessary.
  • Consider inserting the following columns as needed for your school:
    • Number of Absences
    • Major referrals
    • Minor referrals
  • Insert a column labeled “Fall Skills Iowa 5th Grade Math” between “FAY” and “4th Grade National Percentile Rank.”
  • Mastery is set at 75% correct.
    • Student scores that achieved mastery —  color the background of these cells in burnt orange
    • Student scores that did not achieve mastery — color the background of these cells in grey

Detailed Steps for Adding Additional Columns

For the “Teacher” column, type in the teacher’s name in one row. Copy and paste the teacher’s name on each row that corresponds to each of their students.

To insert a new column in the Excel sheet:

  • Place your cursor in the column header where you want to insert new information.
  • With your mouse, click on the column. This will highlight the column
  • Next right click on the column and choose the insert option.
  • This will insert a new column.
  • Add the column header

To color the background of each cell where a student did or did not achieve mastery on an assessment:

  • On the “Home” tab under “Editing” use the Filter option and filter these scores from lowest to highest.
  • Highlight the scores by clicking your mouse on the first cell location and dragging your mouse across all applicable scores
  • Under the “Font” (in the tool bar), select the “Fill color” icon — it will color the background of selected scores.
  • Color the background of students’ scores that did not achieve mastery on the interim assessment as grey
  • Color the background of students’ scores that achieved mastery on the interim assessment as burnt orange

Step 5: Filtering and adding colors to signify proficiency or lack of

Task

  1. If filters do not already exist, add them to each of the columns in the Excel sheet.
  2. If students are proficient, color the background of cells green where students’ Iowa Assessment National Standard Score (NSS) and National Percentile Rank (NPR) scores are located.
  3. If students are not proficient, color the background of cells red where students’ Iowa Assessment National Standard Score (NSS) and National Percentile Rank (NPR) scores are located.

Detailed steps on the above task if needed (and suggestions)

  1. To add filters to each of the columns in the Excel document:
    • Place your cursor on A3 and left click the mouse and highlight each column header across one row.
    • In the toolbar under “Editing” within “Sort & Filter,” click on “Filter”
    • This will add filters to each column.

This will allow you to filter by any column and keep each student’s data intact across all columns.

  1. Color the background of students’ Iowa Assessment National Standard Score (NSS) and National Percentile Rank (NPR) scores green if they are proficient:
    • Filter the “Achievement Levels” column, from lowest to highest.
    • Highlight the scores by clicking your mouse on the first cell location and dragging your mouse across all applicable scores.
    • Under the “Font” (in the tool bar), select the “Fill color” icon — it will color the background of selected scores.
    • Color the background of students’ scores that were proficient as green.
  2. Color the background of students’ Iowa Assessment National Standard Score (NSS) and National Percentile Rank (NPR) scores red if they are not proficient:
    • Follow the above process

Step 6: 4th Grade proficiency at the building level

Determine the number of 4th grade students scoring proficient on the Iowa Assessments at the building level and then enter these numbers into the chart (location Y94) to determine the percent of students who scored proficient at the building level. Repeat the process at the 5th grade when the data is available.

4th Grade Building Level: 54.7%

Detailed steps on filtering the NSS score from the lowest to highest:

  1. Place your cursor in column T on the first student’s “4th Grade National Percentile Rank” or column U on the first student’s “4th Grade National Standard Score.” (Note: these two scores have a correlation of 1.0)
  2. Click your mouse on the first score and drag down taking note of the total number of students in the building (75R X 1C) or at the bottom tool bar identified by “Count: 75.”
  3. Place this number (75) in the “Total” cell located in Z94.
  4. Click the filter icon in column U to filter each student’s “4th Grade National Standard Score.”
  5. Select “Sort Smallest to Largest” this will sort student scores.
  6. Click your mouse on the first proficient score and drag down to the last proficient score in the building (41R X 1C) or at the bottom tool bar identified by “Count 41.”
  7. Place this number (41) in the “Number” cell located in cell Y94.
  8. The percent of students scoring proficient will automatically appear in cell location X94 because a formula had been typed into that cell (54.7%).

Step 7: 4th grade proficiency at the teacher level

Determine the number of 4th grade students who scored proficient on the Iowa Assessments at the teacher level and enter these numbers into the chart (location W83) to determine the percent of students scoring proficient at the teacher level

  • Teacher Jones Level: 56.0%
  • Teacher Smith Level: 60.0%
  • Teacher Long Level: 48.0%

View detailed Steps for 4th Grade Proficiency at the Teacher Level:

Filter first by teacher

  1. To do this click the filter icon located in column A (cell A3).  Select teacher Jones by unchecking teacher Smith, Long and the blanks option OR by unselecting “Select All” and then selecting teacher Jones.
  2. Click OK- this will select only teacher Jones’ students.
  3. Click the filter icon in column U to filter each student’s “4th Grade National Standard Score.”
  4. Select “Sort Smallest to Largest,” this will sort student scores.

With scores filtered by NSS score from smallest to largest

  1. Place your cursor in column T on the first student’s “4th Grade National Percentile Rank” or column U on the first student’s “4th Grade National Standard Score.” (Note: these two scores have a correlation of 1.0)
  2. Click your mouse on the first student’s score and drag down to the last student’s score taking note of the total number of students in this teacher’s class. This number is identified on the bottom tool bar by “Count: 25.” Place this number (25) in the “Total” cell located in Z86.  You will have to unsort teacher to see these cells (click the filter button in A3 and select “Clear Filter from Teacher” or check the “Select All” box).
  3. Click your mouse on the first student who scored proficient and drag down to the last student’s proficient score taking note of the total number of proficient students in this teacher’s class.  This number is identified on the bottom tool bar by “Count: 14.” Place this number (14) in the “Number” cell located in Y86.   You will have to unsort teacher to see these cells.
  4. The percent of students scoring proficient will automatically appear in cell location X86 because a formula had been typed into that cell (56.0%).

Repeat this process for each teacher.


Step 8: 5th grade mastery at the teacher level

Determine the number of 5th grade students who achieved mastery on the Fall Skills Iowa Math at the teacher level and enter these numbers into the chart (location W98) to determine the percent of students achieving mastery at the teacher level

  • Teacher Jones Level: 44.0%
  • Teacher Smith Level: 48.0%
  • Teacher Long Level: 48.0%

Determine the number of 5th grade students who achieved mastery on the CBM-R at the teacher level and enter these numbers into the chart (location W105) to determine the percent of students achieving mastery at the teacher level

  • Teacher Jones Level: 52.0%
  • Teacher Smith Level: 56.0%
  • Teacher Long Level: 44.0%

Determine the number of 5th grade students who achieved mastery on the a-Reading at the teacher level and enter these numbers into the chart (location W112) to determine the percent of students achieving mastery at the teacher level

  • Teacher Jones Level: 48.0%
  • Teacher Smith Level: 48.0%
  • Teacher Long Level: 40.0%

Detailed Steps for 5th Grade Mastery at the Teacher Level:

Filter first by teacher

  1. To do this click the filter icon located in column A (cell A3). Select teacher Jones by unchecking teacher Smith, Long and the blanks option OR by unselecting “Select All” and then selecting teacher Jones.
  2. Click OK- this will select only teacher Jones’ students.
  3. Click the filter icon in column M to filter each student’s “Fall Skills Iowa 5th Grade Math” score.
  4. Select “Sort Smallest to Largest,” this will sort student scores.

With scores filtered by “Fall Skills Iowa 5th Grade Math” from smallest to largest

  1. Place your cursor in column M on the first student’s “Fall Skills Iowa 5th Grade Math” score.
  2. Click your mouse on the first student’s score and drag down to the last student’s score taking note of the total number of students in this teacher’s class. This number is identified on the bottom tool bar by “Count: 25.” Place this number (25) in the “Total” cell located in X100.
  3. Click your mouse on the first student who achieved mastery and drag down to the last student’s mastery score taking note of the total number of students who achieved mastery in this teacher’s class.  This number is identified on the bottom tool bar by “Count: 11.” Place this number (11) in the “Passed” cell located in Y100.
  4. The percent of students achieving mastery will automatically appear in cell location Z100 because a formula had been typed into that cell (44.0%).

Repeat this process for each teacher and for each test score (CBM-R & a-Reading)


Step 9: Prediction validity

To determine the prediction validity of the Interim Assessment, refer to the Using Interim Assessments to Predict Summative Outcomes section of the website.


Step 10: Answer the three data questions relative to and across each assessment

  • What does the data tell us?
  • What are we going to do about it?
  • Is it working?