TIE533 Benchmark Overview

To fulfill the requirements of this course, students must complete a four-part project. The four parts are:

1. Investigate and summarize the practices and procedures your school district employs for student assessment data including collection, analysis, and use.


2. Develop and document a tool (see detail below), built with advanced features of productivity applications, that provides some decision-making support related to instructional planning or practice.


3. Develop and deliver a presentation to your peers describing the tool and its applications.


4. Describe the assistance you provided to your colleagues in collecting and analyzing data, and communicating findings.

Overview Screencast

Development

To create part one, students should identify the key staff members to be interviewed in order to collect the pertinent details regarding student assessment data and the way in which it is collected, analyzed, and used. A summary of each interview including questions and responses should be included as artifacts. The interviews should identify what standardized test scores, local assessments, and other related student assessment data are collected. Additionally, the interviews should identify which person(s) in the district work directly with the data, what tools are used for analysis, how the data and analytical assessments are reported, and how this process supports instructional planning and practice.

For part two, students should identify an element of the assessment data management practice which would benefit from the creation of a “tool” or “template” that would enhance the process and make the data more readily useful for instructional planning and practice. Such a “tool” should consist of any combination of worksheet (spreadsheet) templates, data base templates, and data collection components, though other elements may be included. The tool should be created and tested with sample data and reviewed by a potential user for its effectiveness.

Part three calls for a presentation (10-15 minutes) to be delivered to your peers that describes the tool developed for part two. The presentation should indicate how the tool is constructed, how it is to be applied to data, how it fits into the overall district assessment program, and how it can be applied to instructional planning and practice.

Part four should be a short description and reflection of the project. The entry should include any assistance you provided to colleagues around the development, analysis, and communication of your findings.

The products for this project will consist of a written summary report, including from part one, artifacts from the interviews, related documentation, and a table (organized by grade-level and indicating the type and source of assessment data) describing the district’s practices for assessment data management. The report will also include samples of the tool created for part two, examples of the tool’s application to sample data, and a review of the tool’s value as described by its potential user(s). Part four will be included as the narrative and reflection of the collaboration and communication of findings to colleagues. Finally, a copy of the presentation from part three is to be included.

You can review Cindy's final benchmark assignment in the resources to see what I'm looking for.

Getting Started: Excel Projects

To create your benchmark assignment, you'll be using Excel. There are 2 projects that will help you review various features of Excel. First you'll be creating an interactive gradebook. Second you'll be using ISAT data for a 4th Grade class to work on measures of variance and central tendency. They projects do overlap, you learn the skill in the gradebook since it matches the screencasts I've made, then you practice on the ISAT data on your own to make sure you can do it. You don't have to do both if you feel you have got the process down...I'm not collecting either in the Course by Arrangment...it's for your knowledge / skill development. If you're already doing great with Excel...you can breeze through these and jump straight into the final benchmark assignment.

Download the Excel Project Sheet from the resources, left side of blog, to create the Interactive Gradebook. The next few screencasts will walk you through the process.

Randy

Interactive Gradebook Project - builds basic Excel Skills

Topics/Activities - download the PDF of the activities

1. Review the Introduction

Unable to display content. Adobe Flash is required.

2. Website overviews of Central Tendency

Read through the following sites to review mean, meadian, mode, and range.

http://www.quickmba.com/stats/centralten/

http://cnx.org/content/m10942/latest/

http://www.une.edu.au/WebStat/unit_materials/c4_descriptive_statistics/central_tendency_measure.html

short video, for high school, but it’s a very simplistic overview of central tendencies

" target="_blank">http://www.youtube.com/watch?v=qw5aJEfqwfU

Be able to define / discuss what is each of these and why they are important statistical analysis of a data set:

• mean
• median
• mode
• range

3. Begin Interactive Gradebook project.

I've included an overview screencast of the interactive gradebook and 2 videos to walk through the first steps of the project. They can be found here:

Unable to display content. Adobe Flash is required.

• Introduction Screencast: http://www.screencast.com/t/tyA79p9K

• Data set for gradebook: http://myclass.nl.edu/tie/tie533/Excel_project.doc

• Tutorial 1: http://www.screencast.com/t/KynTTk6YF

• Tutorial 2: http://www.screencast.com/t/GO3mBsPj

• http://myclass.nl.edu/tie/tie533/CentralTendenciesClassAssignment.pdf

Excel Project II

PDF of Assignment

Next, you are going to continue with exploring features in Excel that will perform calculations and visual representations of your data. Excel is an ‘active’ application that will constantly update tables and graphs as new data has been added. You saw this with the first part of the project by adding new grades to a ‘student’ and having Excel automatically calculate their Grade Point Average (GPA).

You will look at 2 new functions: Conditional Formatting and IF.

• Conditional formatting – offers visual representations of data. For example, highlighting a students ‘status’ for those in the lower portions of class standings.


• IF statement: An advanced function in Excel, which will allow the application to perform calculations and graphing to visually represent data. For example, the “IF” feature will determine if a statement is ‘true’, then perform some action. For this example, we’re going to identify how many students are passing and how many need an ‘intervention’.


• Adding this ‘function’ has 2 purposes. First, to learn how to use the “IF, then” feature to add functionality to your Excel workbooks. Second, to learn the process of adding ‘functions’ to your workbook. Once you learn how to add ‘IF, you can use the same steps to add almost any function to a workbook.

Steps to complete project:

1. Read the overview above.


2. Watch the 2 screencasts on Conditional Formatting and IF.
   
   
   a. Conditional Formatting:
   http://www.screencast.com/t/c1qz7TY1SB0G
   
   
   
   
   
   
   
   b. IF statement: http://www.screencast.com/t/xAJQAglr
   
   
   
   
   
   
   
   
   


3. Open you Excel Interactive Grade book workbook.


4. Using the Conditional Formatting feature to add a color status to students assignment grades. As you see from my example, I have blue for A’s, green for B/C, and yellow for lower then C. Add the conditional formatting to all assignments.


5. Add a conditional formatting to the students overall grade column.


6. Using the IF statement. Add a Column to your grade book next to the students (ID) or name, label it “Status”.


7. Click on the first students ‘Status’ cell and insert the IF function. For students average is greater then 70, list their status as ‘Passing’. If students score is less then 70, list their status as ‘Intervention’.


8. Add a conditional formatting rule to change the color of the cell to highlight ‘Intervention’ students as a ‘red’ cell.

Measures of Variance

Download a PDF of the Assignment Here

So far we’ve been talking about measures of Central Tendency: mean, median, and mode. Central tendency will provide a quick glimpse of the data in terms of where the middle number is, which number occurs the most, and what is the average of all the numbers. Central tendency provides great information that can be used for relations and comparisons. The average can be used to tell how well did one student do compared to the class average? Mode can be used to tell you which test number most of the students missed? Yet, it doesn’t provide a complete picture of the data, for example the mean and median can be the same. Does that mean the data set is the same? No.

Once we have a data set, we can analyze it in many different ways. We want to now look at Measures of Variance. Variance will tell you how spread out your data set is in relation to the mean. Do you have a standard distribution? Normal curve? What? Why is that important?

What is Standard Deviation?
Let’s start with what is a standard deviation. From Wikipedia, “It shows how much variation or "dispersion" there is from the average (mean, or expected value). A low standard deviation indicates that the data points tend to be very close to the mean, whereas high standard deviation indicates that the data are spread out over a large range of values.” http://en.wikipedia.org/wiki/Standard_deviation

A very high SD usually means that your data are too variable and therefore are unreliable.

Best part of the assignment, we can use the Standard Deviation function in Excel to do all the calculations for us. Review the screencast on how to calcualte StDev: http://www.screencast.com/t/B4soGICZaLXz

 

Here’s some more information and background for Standard Deviation before the class assignment:

• Nova Southeastern’s site Review of Standard Deviation: http://www.schoolofed.nova.edu/edl/secure/stats/lesson2.htm


• Understanding Standard Deviation: http://causeandeffectblog.com/?p=102

Assignment: Download and use the data set provided here: ISAT 4th Grade, to:

1. Calculate the Measures of Central Tendency for each content area


2. Calculate the Highest number and the Lowest number for each content area


3. Calculate the Standard Deviation for each content area


4. Post your spreadsheet to the Discussion Board and answer the following question:
   
   What is the Standard Deviation of Earth Sciences and what does that score represent?

TIE533: Correlations

read the overview first, the Excel assignment is at the bottom of the page.

OK, so lets get back to Excel and talk correlations. What’s a correlation? Why is it important in data driven decision making? First, lets set the foundation. When I say correlation, think relationship. How are 2 seemingly different variables related? What is the correlation (relationship) between variable A and variable B? The statistical sign for correlation is 'r', which I'll continue to use in the descriptions.



Let’s set the stage. In Mr. Smith’s classroom, lets say he has been watching students at recess. Some of them run around, are very energetic, playing for the entire recess. At the same time, Mr. Smith has noticed what he thinks might be a ‘drop’ in academic performance of some students in the afternoon sessions. Mr. Smith is wondering if there is any correlation (relationship) between students who are very active at recess and his perceived drop in academic performance in the afternoon?



Yes, it’s a very simplified example, but still it provides a nice context for us to work from and discuss correlations.



A correlation shows relationship, stated in terms between -1.0 and 1.0. Not too much of a range is it? Not really. Still, a perfect relationship is stated a 1.0, meaning there is a direct relationship between 2 variables, such as amount of activity during recess and a drop in performance in afternoon academic sessions. So if true, we'd say the correlation (r) between active recess and afternoon performance is r=1.0, or a positive correlation of 1.0. If there was no relationship between active recess and afternoon performance, what do you think we'd state? It seems a bit misleading, but we'd say r=0. There is no relationship with the correlation calculation is 0. If there were an opposite relationship, or a negative correlation, we'd state it as r= -1.0. Generally speaking, we usually don't see perfect positive or negative correlations, they generally fall someplace in between. Review this site for more information: http://www.socialresearchmethods.net/kb/statcorr.php

Here's a video overview. Although it attempts to apply correlation to psychology, it still is a nice, short overview of what a positive correlation looks like:

What type of data do we need to make a correlation? It's important to know that the only data you can use for a correlation study is Interval Data, or data that has an absolute zero that has meaning. For example, age. When defining your age, 0 is the starting point. Unlike with temperature, where 0 is another number on the scale. You can have a negative temperature, but you can not have negative age.

The stronger your correlation (r value) is to either -1.0 (negative correlation) or 1.0 (positive correlation) the stronger the relationship, in either direction. No correlation (or relationship) would be right in the middle, or 0. Lets say we investigate Mr. Smith's class, collecting data about time running at recess (there is an absolute zero) and their classroom performance after lunch (again the data has an absolute zero). We use Excel to calculate the correlation, and come up with an r=-.9. We would say there is a negative correlation of -.9 between active recess participation and afternoon academic performance.

One of the most important things to remember with measuring correlations is that the relationship, or lack of relationship, doesn't mean there is a cause and effect. Because there is a positive correlation between two variables that doesn't mean that you can definitively state that one causes they other, only that they are related. You need to investigate more on the cause - effect relationship. In our example of Mr. Smith's class, if there were a positive correlation of r= -.9, we can say there is a relationship between active participation at recess and a drop in afternoon performance. What we can't say is that students running around during recess leads to their lower performance, only that there is one. It might be attributed to other factors. Maybe the students are overly sweaty or feel they smell, therefore preoccupied with these thoughts and can't focus on work. More investigation is required once the relationship has been established. Here's a site that will discuss that a bit more: http://www.nvcc.edu/home/healthier/methods/correlation.htm.

What example of two variables of data that you can collect(with an absolute zero) to determine a correlation with your Action Research project or benchmark assignment?

Good news is that Excel will calculate the correlation coefficient for you. You can look up the statistical measures and do it yourself, available here, but after you do, I think you'll agree it's much easier to have an application do it for you instead of by hand!

Lets look at how Excel can calculate the correlation coefficient for us and the discuss what it might mean.

 

---

Excel Assignment

For this project we are going to determine if there are any relationships (correlations) between the Science Scale Score and the rest of the individual science tests. Think about why might you want to look at this type of relationships within one particular content area?

1. Review the screencast on how to calculate the correlation coefficient using Excel.
   
   


2. Using the 4th grade data set from previous Excel projects, calculate the correlations between:
   
   
   
   1. Science Scale score and Science Inquiry.
   
   
   2. Science Scale score and Life Science.
   
   
   3. Science Scale score and Physical Science.
   
   
   4. Science Scale score and Earth/Space Science.
   
   
   5. What do each of the correlations mean?
   
   
   6. Which correlation was the strongest? Were you suprised by that? Why or why not?
   
   
   
   


3. Can you determine if there is a correlation between gender and Life Science? Why or why not?


4. Post your answers to the bottom of this assignment area in the threaded discussion. Be sure to talk about the relationship between each of the scores and what they might mean.


5. Also address, What other correlations might you want to look for? Why?
   
   
   
   1. For example, if you calculated the correlation coefficient for Global perspectives and Government. Would you expect a correlation? What kind? Why?