A Question from the Field...and a Little Excel Lesson

A district administrator recently asked me, "How can I find out how many middle schools in Massachusetts didn't make AYP for Mathematics?"

One source for this information is an Excel document on the ESE AYP page entitled, 2010 Lists of Massachusetts Schools and Districts by NCLB Accountability Status and Accountability and Assistance Level. You can use the filter feature of Excel to answer this (and other) questions.

What is filtering in Excel, you ask? Well, it's a way of displaying ONLY data in a list that fit certain criteria. I encourage you to follow along and try this out, if you're not familiar with the filter feature in Excel, and you'll see just how handy it is!

After you've opened the Excel document above, click the tab at the bottom for Table 5, which will give you a list of all the schools in the state. You can use the filter feature of Excel to narrow down the data to answer this question - just click on the little arrow on the top of the column to use the filter. First, filter in the "Grade Level" column (Column F) to display only the Middle Schools. A number will appear in the lower left corner, telling you how many schools meet that criteria (289 of 1737 schools).

Then, scroll over to Column O (2010 Math AYP Aggregate) and use the filter feature to display only the schools that did not make AYP. Again, look in the lower left corner for the number (136 schools.) You can repeat this process for other criteria, such as subgroups and/or ELA (just make sure you remove the filter in the other column before choosing another column.)

Note that the filter feature works a little differently depending on what version of Excel you have. Here's a simple overview of the filter feature in Excel for those using Excel 2007 or earlier. I find the filter feature to be one of the most useful aspects of exploring and analyzing data with Excel!

November Retest Results and Enrollment Updates Available in EDW

The MCAS November Retest results are now available in the Education Data Warehouse (EDW) in Public Folders > ESE MCAS Reports. The retest data is included in the following student-level reports:

• R-610 Student Test Results


• R-613 Student MCAS History


• R-616 Test Item Analysis Roster


• R-617 Student MCAS Roster

Please note that the retest information is not included in school, district or state aggregated results, so reports that rely on this data will not have the 2010 November Retest prompt option.

In addition to the EDW reports, the MCAS 2010 DropBox also contains PDF student roster reports with retest information.

Finally, the reports in Public Folders > ESE Enrollment and Indicators have been updated to display data from the October 1, 2011 SIMS collection.

2010 Competency Determination Data Released

For those of you involved at the high school level, I thought you'd be interested to know that the 2010 Competency Determination data (MCAS requirements for graduation) has been released. The data reflect MCAS and MCAS-Alt results through the spring 2010 tests and MCAS Performance Appeals through November 18, 2010.

The data files can be found at DropBox Central in the ESE Security Portal for those personnel who have been assigned the MCAS DropBox security role (assigned by your district's Directory Administrator).

For additional information, please see the 1/11 Commissioner's Update.