1 to 5 - Office of School Improvement

Beginner Excel for Data Analysis

June 19 – 22, 2012 Dr. Yuwadee Wongbundhit Curriculum and Instruction

Topics Excel Spread Sheet, Simple Formula, Commands, and Functions (Microsoft Excel 2010) Overview of FCAT 2.0/EOC Data Elements and School Grade

Conversion file: an Excel Tool

Using Excel Filter Function

You...

are familiar with the School Grade System and FCAT 2.0/EOC have some experience in data analysis 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/excel

Designed by Nelson Suarez

Microsoft Excel 2010 Application

Student Database

6

2011-2012 Ideal Data File

7

Student Data Sources

8

Folder and File Structure

2012 FCAT

Reading

Math

Science

Grade 3

Grade 3

Grade 4

Grade 4

Grade 5

Grade 5

Grade 5

Excel Introduction Worksheet

Moving and Scrolling

Ribbons

Row 1,048,576 Rows

Move one cell Arrow keys

File

Column: A to XFD 16,384 Cols.

Move to the beginning of rows/col

Home

Cell – Active Cell 17,179,869,184 Cells

Highlight the area

Insert

Area

Page Layout View 10

Excel Spread Sheet • Go to http://osi.dadeschools.net/excel • Download “2012 Reading Grade 5” • Save the file on the desktop • Open the file

What is the content in each cell? K30

H99

G9

F55

L63

E147

Last cell

J35

L59

Last Cell

H47

B18

L48

G36

D80

F17

H147

B27

C130

K111 12

Practice Excel 1 1. 2.

Insert two empty rows above Row1 – Highlight Row1 and Row2; right click; select insert; adjust column width Type header title as follows; adjust column width; wrap text located in the Alignment group under Home menu Cell

3. 4. 5. 6. 7. 8. 9.

Header Title

Cell

Header Title

A2

Last Name

K2

2011 Achievement Level

B2

First Name

L2

2011 DSS

C2

Student ID

M2

DSS Change

D2

Tested Grade

N2

2012 Raw Score

E2

2012 DSS

O2

2012 % Correct

F2

2012 Achievement Level

P2

% Vocabulary

G2

Vocabulary

Q2

% Reading Application

H2

Reading Application

R2

% Literary Analysis

I2

Literary Analysis

S2

% Informational Text and Research Process

J2

Informational Text and Research Process

T2

2012 L3-5 Performance Points

U2

2012 Learning Gain Points

Cell F1, type “Possible Points”; align text to the right Cell G1, H1, I1 and J1 type 9, 14, 8, and 14 , respectively Freeze Panes: at Cell C3; on the View Menu, select “Freeze Panes” and click “Freeze Panes” Filter Row 2: Highlight Row2. On Data Menu Tab, click Filter Adjust column width; color column header as per your preference; center alignment column headers Highlight a1:U149 and do All Borders Conditional Format Column F and Column K – Color cell in red if less than 3. 13

Understanding FCAT 2.0/EOC Reports?

Understanding FCAT 2.0 and EOC Reports, Spring 2012

14

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 15

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

16

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) 23

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 25

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

26

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)

2012 Content Scores

28

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

29

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%

Sum Formula and Locking Cell Reference Total Items

Student 1 2 3 4 5 6 7

9

14

8

14

Total Reading Literary Informational % Vocabulary Raw Application Analysis Text Correct Score 6 9 7 8 8 10 6 10 6 11 6 9 6 11 6 13 9 12 7 13 7 9 4 9 8 12 5 12

Worksheet tab: Sum and Fill and Lock the cell and fill

32

Practice Excel 2 – 2012 Reading Grade 5

Calculate 2012 Raw Score and 2012 % Correct 1. Cell N1 type formula “=sum(G1:J1)” press ENTER. Result: 45 2. Cell O1 type formula “=N1/N1” press ENTER. Result : 1. In Cell O1, click on % sign under Number of the Home Menu. Result: 100% 3. Highlight Cell N1 and select “Copy” command 4. Click on Cell N3 then Right click and select Paste Formula. 5. In Cell O3, type formula “=N3/N$1” press ENTER. Result: 0.66666667. Click on O3 and click on % sign. Result: 67%

Calculate % Reporting Categories 6. 7. 8. 9.

In Cell P3, type formula “=G3/G$1” press ENTER. Result: 0.66666667; Click on O3 and click on % sign. Result: 67%. Highlight P3 to S3; Fill Right. Result: Cell P3=67%, Cell Q3=64%, Cell R3=88%, and Cell S3=57% Highlight N3 to S3. Then move the cursor to the bottom right border of Cell S3. The cursor should turn into a plus sign, then double click. Cell N3 to Cell S149 should be filled with value. Spot check value for accuracy. Highlight N3 to S149. On the Home menu, select Conditional Formatting, select Highlight Cell Rules, next Less than… and type 51% when the Window “less Than” appear. Next, click OK. Results: All cells with less than 51% should 33be in red.

Practice Excel 3 Filter Feature • Filtered data displays only the rows that meet criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) that you specify and hides rows that you do not want displayed. After you filter data, you can copy, find, edit, format, chart, and print the subset of filtered data without rearranging or moving it.

Click on Drop-Down Arrow in Cell F2 Select “Sort Smallest to Largest” and Click OK Result: the data are sorted by achievement level Click on Drop-Down Arrow in Cell F2 Select “Sort Largest to Smallest” Result: the data are sorted by achievement level Click on Drop-Down Arrow in Cell F2 Select only Level 1 (uncheck others) Results: Only data for Level 1 display.

34

Practice Excel 4 – Simple Analysis Finding the % Correct Range by Level • Filter Level 1 in Cell F2, what is the range of % Correct in column O, writing it down. • Repeat for Level 2, 3, 4 and 5. Result: • Level 1 – 16% to 42% Level 2 – 44% to 67% Level 3 – 64% to 80% Level 4 – 78% to 89%

Level 5 – 91% to 96

Calculating Average Scores 1. In Cell E150, click AutoSum located in the Home menu at the far right under “Editing group”, select AVERAGE, press ENTER. Result: 215.591837. Decrease decimal to 1 using Decrease Decimal located in the number group under the Home menu. 2. Find the average of Column G to J. Result in Cell G150 to J150 3. Find the average of Column N to S. Result in Cell N150 to S150

Calculating High Performance Point Determine point for student with L3-5 1. Clear all filter, next do filter by color in Cell K2 2. Compare the results with column F. Who improve 2012 level? 3. Filter column F with levels 3, 4, 5 4. Next, in Column S, first empty cell, type 1 and highlight the cell to end in column S and fill-down. 5. Filter column F with levels 1, 2 6. Next, in Column S, first empty cell, type 0 and highlight the cell to end in column S and fill-down. 7. Clear all filter Calculate % of student meeting high performance 1. In Cell S150, click AutoSum located in the Home menu at the far right under “Editing group” press ENTER. Result: 79 2. In Cell S151, click AutoSum and change T150 to T149. press ENTER. Result: 147 3. In Cell S152, type “=T150/T151” press ENTER. Result: .53741487. On the Home menu, click % sign.

35

Lunch Break

Please be back on time at 12:30 p.m. Thank you!

Conversion of 2012 FCAT/EOC PDF Student Data Report File to Excel File

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 PDF file. On the File menu, select Save As- Text Name the file and save into the appropriate folder Wait until the export process is completed Close the PDF 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 “inputDataReading” • Spot check data from beginning to end • On worksheet “Direction”, click Reading Data Button • On worksheet “Direction”, click Raw Score Distribution Button • • • •

On File menu, select Save As File name: use appropriate name 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 41

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

42

Annual Measurable Objectives

AYP is gone. AMO is here. Annual Measurable Objectives ESEA Flexibility Waivers http://www.fldoe.org/esea/

Ambitious & Achievable Goal 43

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

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 49

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 2: 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 2: 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

57