Transcript osi.dadeschools.net

Create Actionable Data for Schools,
2012-13
June 26 – 28, 2012
Dr. Yuwadee Wongbundhit
Curriculum and Instruction
Topics
Conversion File from PDF to Excel
Overview of FCAT 2.0/EOC Data Elements
Calculating the Annual Learning Gains and
Performance Points
Data Analysis with Excel Filter and Pivot
Building Database from Multiple Files using Excel Vlookup
Formula
You...
are familiar with the School Grade
System and FCAT 2.0/EOC
have some experience in data
analysis
are comfortable using Excel 2010
love, love, love data!!!
3
Listen to
others
Next, set
Engage
your
learning
into action
with the
ideas
presented
Reflect on
relevance
to you
Norms
Ask
questions
4
Http://osi.dadeschools.net/actiondata
Designed
by
Nelson
Suarez
Conversion of
2012 FCAT/EOC PDF Student Data
Report File to Excel File
Student Report in PDF File
7
Conversion Process
PDF
Combined
Grades
Text
Combined
Grades
Template
by Grade
Level/EOC
Excel Report
by Grade
Level/EOC
8
Folder and File Structure
2012 FCAT
Reading
Math
Science
1. R-Templates
1. M-Templates
1. S-Templates
2. R-PDF
2. M-PDF
2. S-PDF
3. R-Text
3. M-Text
3. S-Text
4. R-Excel
4. M-Excel
4. S-Excel
If you do not have student report in PDF file, use the one from the website.
Instructions for Conversion PDF File
Step 1
Export PDF File to
Text
Step 2
Import Text to
Template file
Step 3
Verify Data
Step 4
Review the Results
Step 5
Save as the file
•
•
•
•
•
Open Adobe Reader.
On the File menu, open the student report, and select Save As- Text
Name the file and save into the PDF folder
Wait until the export process is completed
Close the student report file
• Open the 2012 Action Data Template
• Click on “Import Data”
• Locate the appropriate text file and click Open
• Wait until the import process is completed
• Under “Check your data!”, click on the hyper text
“inputData…”
• Spot check data from beginning to end
• On worksheet “Direction”, click Data Button
• On worksheet “Direction”, click Raw Score Distribution
Button
•
•
•
•
On File menu, select Save As
Use appropriate name (remove “Template 06”
Save as type: select “Excel workbook”
Click Save
Update on School Grade
Florida School Accountability
School •Performance Status
•Annual
Learning
Gains
Grade
AYP
• Performance Status
• Performance by subgroups
%Proficiency Targets
Year
Reading
Math
2009-10
65
68
2009-10
72
74
2010-11
79
80
2011-12
86
86
2012-13
93
93
2013-14
100
100
13
AYP 39 Components Criteria (NA, No, Yes)
Group
Participation Met
(Tested 95%)
Proficiency Met
Other AYP
Criteria
Reading
Math
Reading
Math
TOTAL
1
1
1
1
WHITE
2
2
2
2
BLACK
3
3
3
3
HISP.
4
4
4
4
ASIAN
5
5
5
5
Am. Ind.
6
6
6
6
ED
7
7
7
7
ELL
8
8
8
8
School Grade
SWD
9
9
9
9
Total Group
Writing
Total Group
Graduation
Total Group
14
Annual Measurable Objectives
AYP is gone.
AMO is here.
Annual Measurable Objectives
ESEA Flexibility Waivers
http://www.fldoe.org/esea/
Ambitious & Achievable Goal
15
Elem. & Combination Schools up to grade 7
School Grade of 800 Points
% of students performing at or
above satisfactory level of
success
Reading – 100
% of student making
annual learning gains
Reading - 200
• Overall - 100
• Low 25% - 100
Math – 100
Science – 100
Math - 200
• Overall - 100
• Low 25% - 100
Writing – 100
F: 0-394; D: 395-434; C: 435-494; B: 495-524, A: 525-800
2011-12 through 2013-14 School Grade:
Middle School (900 Points)
% of students performing at or
above satisfactory level of
success
Reading – 100
% of student making
annual learning gains
Reading - 200
• Overall - 100
• Low 25% - 100
Math – 100
Science – 100
Writing – 100
Math - 200
• Overall - 100
• Low 25% - 100
Algebra EOC – 100
• Participation - 50
• Performance - 50
F: 0-444; D: 445-489; C: 490-559; B: 560-589, A: 590-900
2011-12 High School Grade
FCAT Component (800 points)
Non-FCAT (800 points)
Meeting High Standards
Graduation
• Reading (100 Points)
• Math (100 Points)
• Writing (100 Points)
• Overall (200 points)
• At Risk (100 points)
Making Learning Gains
• Reading (200 Points)
• Math (200 Points)
Acceleration Courses
• Participation (150 Points)
• Performance (150 Points)
FCAT Retake Bonus Point (10 Points)
Readiness (200 Points)
• Reading
• Math
• Reading (100 Points)
• Math (100 Points)
F: 0-789; D: 790-869; C: 870-989; B: 990-1049, A: 1050-1600
Understanding FCAT 2.0/EOC
Reports?
Understanding FCAT 2.0 and EOC Reports, Spring 2012
19
FCAT 2.0 Scores Reading/Math
1996 SSS
2007 NGSSS
Base scale of FCAT
Base scale of FCAT 2.0
FCATFCAT
19992001FCAT
2002FCAT
2010
2011
FCAT 2.0
Equivalent
Scores
20
FCAT 2.0 Scores, Reading/math
FCAT 2.0
2014
FCAT 2.0
2013
FCAT 2.0
2011
FCAT 2.0
2012
2010-11 Baseline Year (Retrofitted Scores)21
FCAT 2.0 Reading-Math Scores
Raw Score
Content Area Scores
V
Developmental Scale Score (DSS)
(140 to 302 R: G3-10)
(140 to 298 M: G3-8)
IR
Raw
Score
RA
LA
Achievement
Level
(1 to 5)
V:
RA:
LA:
IR:
Vocabulary
Reading Application
Literary Analysis
Informational
Text/Research Process
2012 Grade 9, FCAT 2.0 Reading
Student Name and FL ID
2012
DSS
2012 Ach.
Level
2012 Content
Scores by
Reporting
Category
13
30
25
22
21
14
35
23
28
28
Raw Scores
2011
Scores:
Level,
DSS,
DSS Change
Developmental Scale Score (Reading)
Grade
Level 1
Level 2
Level 3
Level 4
Level 5
3
140-181
182-197
198-209
210-226
227-260
4
154-191
192-207
208-220
221-237
238-269
5
161-199
200-215
216-229
230-245
246-277
6
167-206
207-221
222-236
237-251
252-283
7
171-212
213-227
228-242
243-257
258-289
8
175-217
218-234
235-248
249-263
G10 264-296
9
178-221
222-239
240-252
253-267
268-302
10 Min
Raw
Score
188-227
Max
228-244
245-255
256-270
271-302
140
G3
G6
Developmental Scale Scores
Grade 3
302
Grade 10
Developmental Scale Score (Math)
Grade
Level 1
Level 2
Level 3
Level 4
Level 5
3
140-182
183-197
198-213
214-228
229-260
4
155-196
197-209
210-223
224-239
240-271
5
163-204
205-219
G6220-233
234-246
247-279
6
170-212
213-226
227-239
240-252
253-284
7
179-219
8 Min
187-228
Raw
Score
140
G3
G8
220-233
234-247
248-260
261-292
229-240
Max
241-255
256-267
268-298
Developmental Scale Scores
Grade 3
298
Grade 10
FCAT 2.0/EOC Achievement Level Policy Definitions
5. Demonstrate mastery of the most
challenging content of the NGSSS.
4. Demonstrate an above satisfactory
level of success with the challenging
content of the NGSSS.
3. Demonstrate a satisfactory level of
success with the challenging content of the
NGSSS.
2. Demonstrate a below satisfactory level
of success with the challenging content of
the NGSSS.
1. Demonstrate an inadequate level of
success with the challenging content of the
NGSSS.
FCAT 2.0/EOC Achievement Level Policy Definitions
FCAT
FCAT 2.0 and EOC
Level
1
Student has little success with
the challenging content of the
SSS.
Demonstrate an inadequate level of
success with the challenging content
of the NGSSS.
Level
2
Student has limited success with
the challenging content of the
SSS.
Demonstrate a below satisfactory
level of success with the challenging
content of the NGSSS.
Level
3
Student has partial success with
the challenging content of the
SSS, but performance is
inconsistent.
Demonstrate a satisfactory level of
success with the challenging content
of the NGSSS.
Student has success with the
challenging content of the SSS.
Demonstrate an above satisfactory
level of success with the challenging
content of the NGSSS.
Student has success with the
most challenging content of the
SSS. A student scoring in Level 5
answers most of the test
questions correctly, including the
most challenging questions.
Demonstrate mastery of the most
challenging content of the NGSSS.
Level
4
Level
5
2012 FCAT 2.0 Science
Raw Score
Equivalent Scale
Score
(100 - 500)
Content Scores
C1
C4
Raw
Score
C2
C3
Equivalent
Achievement
Level
(1 to 5)
28
Geometry and Biology EOC Scores
Raw Score
Content Scores
C1
T-Score Scale
(20 to 80)
Raw
Score
C3
Statewide
Comparison by
Thirds
Statewide by
Thirds
T-Score Scale
Range
1
20-45
2
46-54
3
55-80
C2
Scale Score
Test Form
Statewide
Comparison
1
2
Points Earned
by Content
Area
3
Student Name
and ID
30
2012 Geometry EOC Scale Distribution - Statewide
2012 Geometry EOC Distribution
Number of Students
9000
8000
7000
6000
5000
35% of Students at
the bottom third
(MDCPS: 44%)
34% of Students
at the top third
(MDCPS: 27%)
4000
3000
2000
1000
0
20212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980
EOC Scale Scores
Statewide by Thirds
T-Score Scale Range
1
20-45
2
46-54
3
55-80
31
2012 Algebra 1 EOC Scores
Raw Score
Algebra Content Scores
C1
EOC Scale Score
(325 - 475)
Raw
Score
C3
Achievement Level
(1 to 5)
Level 1
Level 2
C2
Level 3
Level 4
Level 5
325-374
375-398
399-424
(49)
(23)
(25)
Source:
FL DOE/Office of
Assessment
425-436
(11)
437-475
(38)
Worksheet 1: Calculate the % correct for each content area.
Total Points
Number of Points Possible
School
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Raw Score
% Corr.
39
100%
Points Earned By Content Area
% Correct By Content Area
7
21
11
100%
100%
100%
Content
1
Content
2
Content
3
% Content
1
% Content
2
% Content
3
6
5
4
4
5
6
4
4
4
5
4
3
4
6
4
5
4
3
4
4
5
5
3
4
5
17
17
12
13
17
18
13
21
10
14
9
10
8
10
11
14
19
9
12
15
13
19
17
5
13
8
9
7
6
9
9
7
7
5
7
4
5
7
7
6
7
7
6
6
6
7
3
9
7
5
33
Worksheet 1: Calculate the % correct for each content area.
Total Points
Number of Points Possible
School
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Points Earned By Content Area
7
21
11
Raw Score
% Corr.
39
100%
Content
1
Content
2
Content
3
31
31
23
23
31
33
24
32
19
26
17
18
19
23
21
26
30
18
22
25
25
27
29
16
23
79
79
59
59
79
85
62
82
49
67
44
46
49
59
54
67
77
46
56
64
64
69
74
41
59
6
5
4
4
5
6
4
4
4
5
4
3
4
6
4
5
4
3
4
4
5
5
3
4
5
17
17
12
13
17
18
13
21
10
14
9
10
8
10
11
14
19
9
12
15
13
19
17
5
13
8
9
7
6
9
9
7
7
5
7
4
5
7
7
6
7
7
6
6
6
7
3
9
7
5
% Correct By Content Area
100%
100%
100%
% Content % Content % Content
1
2
3
86%
81%
73%
71%
81%
82%
57%
57%
64%
57%
62%
55%
71%
81%
82%
86%
86%
82%
57%
62%
64%
57%
100%
64%
57%
48%
45%
71%
67%
64%
57%
43%
36%
43%
48%
45%
57%
38%
64%
86%
48%
64%
57%
52%
55%
71%
67%
64%
57%
90%
64%
43%
43%
55%
57%
57%
55%
57%
71%
55%
71%
62%
64%
71%
90%
27%
43%
81%
82%
57%
24%
64%
71%
62%
45%
Data Analysis Questions
1. How many students on this report?
2. How many students scoring at each achievement level in 2012? What are the
%? Who scored in Level 1?
3. How many students scoring at each achievement level in 2011 and 2012?
4. How many students scoring at or above satisfactory levels in 2012? Which
student?
5. What is the range of % correct by achievement level?
6. Which student performed below 51% correct in Vocabulary, Reading
Application, Literary Analysis, and/or Informational Text and Research
Process? How many students?
7. Which student performed above 70% correct in Vocabulary, Reading
Application, Literary Analysis, and/or Informational Text and Research
Process? How many students?
8. What is the average % correct for each reporting category by achievement
level?
9. How many points for making annual learning gains?
10.How many points for meeting at or above satisfactory level?
2012 % Correct Range by FCAT 2.0 Achievement Level
Grade
Level 1
Level 2
Level 3
Level 4
Level 5
Levels: 3-5
5
45
52
37
15
4
56
5
18%-51%
44%-67%
64%-80%
82%-91%
91%-93%
64%-93%
2012 % Correct of Reporting Category by FCAT 2.0 Achievement Level
# of
# of
Stu >=
Stu <
70%
51%
Avg.
%
Corr.
Avg.
%
Corr.
Level
1
Avg.
%
Corr.
Level
2
Avg.
%
Corr.
Level
3
Avg.
%
Corr.
Level
4
Avg.
%
Corr.
Level
5
Grade
Reporting
Category
5
V
60
56
59%
34%
56%
80%
89%
92%
5
5
5
RA
IA
IR
67
60
69
54
67
48
58%
63%
56%
37%
43%
34%
54%
61%
54%
73%
76%
68%
87%
88%
83%
95%
88%
93%
Total Students: 153
Worksheet 2: Looking at the Data
2012 % Correct Range by FCAT 2.0 Achievement Level
Grade
Level 1
Level 2
Level 3
Level 4
Level 5
Levels: 3-5
2012 % Correct of Reporting Category by FCAT 2.0 Achievement Level
Grade
Reporting
Category
# of
# of
Stu >=
Stu <
70%
51%
Avg.
%
Corr.
Avg.
%
Corr.
Level
1
Total Students:___
Avg.
%
Corr.
Level
2
Avg.
%
Corr.
Level
3
Avg.
%
Corr.
Level
4
Avg.
%
Corr.
Level
5
2012 % Correct Range by FCAT 2.0 Achievement Level – Reading
(Sample based on data from couple of schools)
Grade
Level 1
Level 2
Level 3
Level 4
Level 5
3
0%-49%
49%-71%
71%-82%
82%-93%
93%-100%
4
0%-49%
44%-69%
67%-80%
82%-91%
96%-100%
5
0%-51%
44%-67%
64%-80%
82%-91%
91%-100%
6
0%-51%
42%-62%
62%-80%
80%-84%
91%-100%
7
0%-58%
49%-73%
69%-87%
84%-93%
91%-100%
8
0%-51%
49%-71%
67%-84%
80%-91%
89%-100%
9
0%-56%
44%-71%
64%-82%
80%-91%
89%-100%
10
0%-47%
42%-64%
62%-76%
73%-89%
87%-100%
2012 % Correct Range by FCAT 2.0 Achievement Level – Math
(Sample based on data from couple of schools)
Grade
Level 1
Level 2
Level 3
Level 4
Level 5
3
0%-48%
45%-68%
68%-86%
86%-93%
93%-100%
4
0%-53%
53%-70%
68%-85%
83%-95%
95%-100%
5
0%-43%
35%-57%
57%-74%
72%-79%
89%-100%
6
0%-36%
30%-52%
50%-73%
73%-89%
89%-100%
7
0%-41%
30%-55%
50%-73%
68%-86%
84%-100%
8
0%-40%
35%-56%
50%-73%
69%-88%
85%-100%
ALG
0%-26%
13%-37%
31%-61%
57%-74%
78%-100%
Annual Learning Gains
Annual Learning Gains
Performance
Performance
2011
FCAT
Baseline
.
Fall Interim
Assessment
School Year
Winter Interim
Assessment
2012 Current
FCAT
3-Ways to Make Annual Learning Gains
1. Improve
achievement
level
2. Maintain
Achievement
Level
3. Demonstrate a
year’s growth for
students remain with
low proficient levels
FCAT 2.0
Achievement
Level
FCAT 2.0 L3-5
(3-3, 4-4, 5-5)
FCAT 2.0:
1-1, 2-2
EOC Assessment
Achievement
Level
EOC L3-5
(3-3, 4-4, 5-5)
EOC: 1-1, 2-2
FAA
Performance
Level
FAA – L 4-9
(4-4, 5-5, 6-6,
7-7, 8-8, 9-9)
FAA: 1-1, 2-2,
3-3
42
Draft-Annual Learning Gains for FCAT 2.0
Start
Additional Weight for DSS Gains
1.0 Points
1.1 Points
Example: A 4th grader scoring at level 1 in prior-year would
need to increase score by at least 16 DSS points to qualify
for 1.1. (12+ 33% more than 12 = 16)
Draft-Annual Learning Gains for EOC
Start
Draft-Annual Learning Gains for FAA
Start
Reading Learning Gains
2011 FCAT
Tested
Grade
2012 FCAT
Tested
Grade
2011 FCAT
2.0 Level
2012 FCAT
2.0 Level
DSS
Change
2012 LG
?
3
4
Level 5
Level 5
-2
Y?
6
7
Level 4
Level 3
5
N?
9
10
Level 3
Level 3
-8
Y?
3
3
Level 1
Level 1
11
N?
10
10
Level 2
Level 2
5
N
?
Worksheet 3: Learning Gains – FCAT 2.0 Reading
Student
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
2011
Tested
Grade
10
9
9
3
7
9
3
6
4
10
5
4
8
6
3
7
10
5
7
8
8
5
4
6
2012
Tested
Grade
10
10
9
4
8
10
4
7
5
10
6
5
9
7
3
8
10
6
8
9
9
6
4
7
2011 FCAT 2012 FCAT 2011 FCAT 2012 FCAT
DSS Change
2.0 DSS
2.0 DSS
2.0 Level
2.0 level
215
221
231
225
250
185
238
230
240
210
221
241
225
145
250
225
205
175
245
269
162
179
200
219
245
235
221
230
256
210
240
247
244
217
227
224
260
158
249
228
237
181
242
256
170
190
215
1
1
2
4
NM
3
2
4
4
2
2
4
3
3
1
4
1
2
1
3
5
1
1
1
1
3
2
4
2
4
3
3
5
2
2
3
2
5
1
4
2
4
1
3
4
1
1
2
4
24
4
-4
NA
6
25
2
17
4
7
6
-17
35
13
-1
3
32
6
-3
-13
8
11
15
2012 LG
Points
2012 PF
Point
Worksheet 3: Learning Gains – FCAT 2.0 Reading
Student
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
2011
Tested
Grade
10
9
9
3
7
9
3
6
4
10
5
4
8
6
3
7
10
5
7
8
8
5
4
6
2012
Tested
Grade
10
10
9
4
8
10
4
7
5
10
6
5
9
7
3
8
10
6
8
9
9
6
4
7
2011 FCAT 2012 FCAT 2011 FCAT 2012 FCAT
DSS Change
2.0 DSS
2.0 DSS
2.0 Level
2.0 level
215
221
231
225
250
185
238
230
240
210
221
241
225
145
250
225
205
175
245
269
162
179
200
219
245
235
221
230
256
210
240
247
244
217
227
224
260
158
249
228
237
181
242
256
170
190
215
1
1
2
4
NM
3
2
4
4
2
2
4
3
3
1
4
1
2
1
3
5
1
1
1
1
3
2
4
2
4
3
3
5
2
2
3
2
5
1
4
2
4
1
3
4
1
1
2
4
24
4
-4
NA
6
25
2
17
4
7
6
-17
35
13
-1
3
32
6
-3
-13
8
11
15
2012 LG
Points
2012 PF
Points
0
1.1
0
1
NA
1.1
1.1
0
1.2
0
0
0
0
1.2
1
1
1
1.1
0
1
0
0
0
1.1
0
1
0
1
0
1
1
1
1
0
0
1
0
1
0
1
0
1
0
1
1
0
0
0
Lunch
Break
Please be back on time at 12:30 p.m.
Thank you!
Instructions for Calculating LG Points
Preparation Steps
1. Insert two empty columns:
Step 1 – Highlights Columns R and S, right click, then click on Insert
Step 2 – On the Home menu, click on Conditional Formatting,
and select “Clear Rules from Selected Cells”.
Next, under Number group of the Home menu, select “General”.
Next, under Font group of the Home menu, select Borders and select All Borders.
Step 3 – In Cell R2, type “Learning Gains Points”
Step 4 – In Cell S2, type “Performance L3-5 Points”
2. Hide all columns with the exception of the following:
• Student Name ( Column A),
• 2012 DSS (Column C),
• 2012 Level (Column D),
• 2011 level (Column I),
• 2011 DSS (Column J),
• DSS Change (Column K),
• Learning Gains Points (Column R)
• Performance L3-5 Points (Column S)
3. Highlight Cell A2 to S2. On the DATA menu, under Sort & Filter group, click Filter twice.
4. Cell A2, filter out “Blanks” from Student Name.
Instructions for Calculating LG Points
Follow the leaning gain flow chart to determine learning gains points
1. Filter “NM and NA” 2011 Level. Next, in the first empty cell under “Learning Gains Points”, type “NA” and highlight to the last cell. Next, fill down.
Clear filter from 2011 Level.
2. Increase achievement level one or more in 2011 level to Level 5 in 2012:
– Step 1: Filter 5 under 2012 Level and 1-4 under 2011 level
– Step 2: Column “Learning Gains Points”, in the first empty cell, type “1.2” and highlight to the last cell. Next, fill down.
– Step 3: Clear filter from 2011 Level and 2012 Level
3.
Increase achievement level one or more in 2011 level to Level 4 in 2012:
– Step 1: Filter 4 under 2012 Level and 1-3 under 2011 level
– Step 2: Column “Learning Gains Points”, in the first empty cell, type “1.1” and highlight to the last cell. Next, fill down.
– Step 3: Clear filter from 2011 Level and 2012 Level
4.
Increase achievement level from Level 1 in 2011 to Level 3 in 2012:
– Step 1: Filter 3 under 2012 Level and 1 under 2011 level
– Step 2: Filter DSS change greater than or equal to the 33% more than the min. required gain. See table 33% point gain.
– Step 3: Column “Learning Gains Points”, in the first empty cell, type “1.1” and highlight to the last cell. Next, fill down.
– Step 4: Clear filter from DSS Change and this time filter value less than 33% more than the required gain.
– Step 6: Column “Learning Gains Points”, in the first empty cell, type “1.0”, highlight to the last cell. Next, fill down
– Step 7: Clear filter from DSS Change, 2011 Level, and 2012 Level
5.
Increase achievement level from Level 2 in 2011 to Level 3 in 2012: Follow steps in item 4
6.
Increase achievement level from Level 1 in 2011 to Level 2 in 2012: Follow steps in item 4
7.
Maintain Level 3-3, Level 4-4, and Level 5-5
– Step 1: Filter 3 under 2012 Level and 3 under 2011 Level
– Step 2: Column “Learning Gains Points”, in the first empty cell, type “1.0”, highlight to the last cell. Next, fill down
– Step 3: Clear filter from 2012 Level and 2011 Level
– Repeat above steps 1 to 3 for L4-4 and L5-5
8.
Retain at Level 1-1 and Level 2-2
– Step 1: Filter 1 under 2012 Level and 1 under 2011 level
– Step 2: Filter DSS change greater than or equal to the 33% more than the min. required gain. See table 33% point gain.
– Step 3: Column “Learning Gains Points”, in the first empty cell, type “1.1”, highlight to the last cell. Next, fill down
– Step 4: Clear filter from DSS Change and this time filter value at or higher than the min. required gain and less than 33% point gain.
– Step 5: Column “Learning Gains Points”, in the first empty cell, type “1.0”, highlight to the last cell. Next, fill down
– Step 6: Clear filter from DSS Change and this time filter value less than the min. required gain
– Step 7: Column “Learning Gains Points”, in the first empty cell, type “0”, highlight to the last cell. Next, fill down
– Step 8: Clear filter from DSS Change, 2011 Level, and 2012 Level
– Repeat above steps 1 to 8 for retain at Level 2-2
9.
Under Learning Gains Points, Filter Blanks. In the first empty cell, type “0”, highlight to the last student. Next, fill down.
10. Next, clear filter from Learning Gains Points
Pivot Table
Tool for
Data
Analysis
53
Instructions for Creating and Using Pivot Table for Data Analysis
Prepare Data for Pivot Table:
•
Open the Excel Practice file. Next on Cell A2, filter out “Blanks” from “Student Name”
•
Highlight from A2:S155
Crate Pivot Table:
•
On the Insert menu, click PivotTable. The window “Create PivotTable” will appear. Take a look at Table/Range (It should look like this:
’2012 Reading Learning Gains’!$A$2:$S$155). The range must cover all your data with the first row contains field names. Next, click OK
•
On the new sheet a Pivot Table is created. Rename this sheet such as “Pivot Table”
•
Click in the PivotTable area, Right click then select PivotTable Options. The PivotTable Options Window appears.
•
Select “Display” Tab, make sure that the “Classic Pivot Table Layout” is checked. Click OK
•
You should see the “Pivot Table Field List” at the right, if not, click anywhere in the Pivot Table Area.
•
You can analyze your data; begin by answering the data question. See questions 1-13.
Calculate % value:
•
From “Pivot Table Field List”, drag “FL ID” drop into “Value” box. You will see two columns that show the count values.
•
Right click in the PivotTable Area under the second values count of student ID, then select “Value Field Settings”. The window of Value Field
Settings appears.
•
Click on tab “Show Values As”. Under “Show Values As” drop down arrow, select “% of Column Total”. Next click “Number Format”. Adjust
the decimal places to 0. Next, click OK and click OK again.
Calculate the average % correct:
•
Click the value under the calculated field, right click and select “Value Field Setting”, the window “Value Field Settings will appear.
•
Under “Summarize Values By” , select “Average”, then at the bottom , click “Number Format”. The “Format Cells” window will appear.
•
Select Custom and under “General”, click 0 then click OK and click OK again.
54
Data Analysis Questions
How many students on this report?
How many students scoring at each achievement
level in 2012? What are the %? Who scored in Level
1?
How many students scoring at each achievement level in 2011
and 2012?
How many students scoring at or above satisfactory levels in
2012? Which student?
What is the range of % correct range by achievement level?
Data Analysis Questions
Which student performed below 51% in Vocabulary, Reading
Application, Literary Analysis, and/or Informational Text and
Research Process? How many students?
Which student performed above 70% in Vocabulary, Reading
Application, Literary Analysis, and/or Informational Text and
Research Process? How many students?
What is the average % correct for each reporting category by
achievement level?
How many points for making annual learning gains?
How many points for meeting at or above satisfactory level?
Student Database
57
2011-2012 Ideal Data File
58
Student Data Sources
“Student ID” is used as a lookup value to merge the data from different files using
Excel formula “V-Lookup”
=VLOOKUP(LOOKUP_VALUE, TABLE_ARRAY, COLUMN INDEX NUMBER, FALSE)
59
VLOOKUP FUNCTION
,
,
,
=VLOOKUP(LOOKUP_VALUE TABLE_ARRAY COLUMN INDEX NUMBER FALSE)
Lookup_Value:
What value are
you searching
for?
Excel will look for
a match to this
value in the
leftmost column
of the lookup
table
Table_Array:
Where do you
want to search?
Use absolute
references to
“lock” the range
by pressing F4
key
Col_index_num
Which column
contains the
search result?
Count over
from the first
column to
figure out what
this number
should be,
starting with 1.
FALSE
To force Excel
to lookup value
be exact match
=VLOOKUP(b2,‘Homeroom'!$A$2:$B$37,2,FALSE)
VLOOKUP Formula
1Column
2 Index
3 Number
4
5
A
1
Student ID
2
4444444
3
5555555
4
1111111
5
7777777
6
1515151
7
9999999
8
1010101
9
2020202
10
1212121
11
3333333
B
A
C
2012 Winter
Level
PF
3
5
1
1
4
2
3
5
5
5
Worksheet “2012 FCAT”
1
B
C
D
E
Student
Fall IA
Name Grade
Gender
ID
PF
F
G
Winter
ELL
IA PF
2
1818181
I
S
3
1212121
S
S
4
6666666
I
L
5
11111111
L
I
6
22222222
S
S
7
33333333
S
S
8
4444444
L
S
9
5555555
L
S
10
7777777
I
I
11
12
13
14
8888888
9999999
1010101
1313131
L
S
S
L
L
L
S
I
Worksheet “Interim PF”
=VLOOKUP(LOOKUP_VALUE, TABLE_ARRAY, COLUMN INDEX NUMBER, FALSE)
C2 =vlookup(A2,‘Interim PF’!$C$2:$G$14,5,false)
61
Download File Download Manager
63