Beginner Excel for Data Analysis

1. Beginner Excel for Data Analysis June 19 – 22, 2012 Dr. Yuwadee Wongbundhit Curriculum and Instruction

2. Topics

3. Assumptions: You...

4. NORMS

5. Http://osi.dadeschools.net/excel Microsoft Excel 2010 Application

6. Student Database

7. 2011-2012 Ideal Data File

8. Student Data Sources

9. Folder and File Structure

10. Excel Introduction

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

12. What is the content in each cell?

13. Practice Excel 1 • 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 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.

14. Understanding FCAT 2.0/EOC Reports? Understanding FCAT 2.0 and EOC Reports, Spring 2012

15. FCAT 2.0 Scores Reading/Math 1996 SSS 2007 NGSSS Base scale of FCAT Base scale of FCAT 2.0 FCAT 1999 FCAT 2001 2011 FCAT 2.0 Equivalent Scores FCAT 2002 FCAT 2010

16. FCAT 2.0 Scores, Reading/math FCAT 2.0 2014 FCAT 2.0 2013 FCAT 2.0 2012 FCAT 2.0 2011 2010-11 Baseline Year

17. FCAT 2.0 Reading-Math Scores Raw Score Content Area Scores Developmental Scale Score (DSS)(140 to 302 R: G3-10) (140 to 298 M: G3-8) Achievement Level(1 to 5) V: Vocabulary RA: Reading Application LA: Literary Analysis IR: Informational Text/Research Process

18. 2012 Grade 9, FCAT 2.0 Reading Student Name and FL ID 2012 DSS 2012 Ach. Level 2012 Content Scores by Reporting Category 2011 Scores: Level, DSS, DSS Change 13 30 25 22 21 14 35 23 28 28 Raw Scores

19. Developmental Scale Score (Reading) G6 G3 G10 Min Max Raw Score 140 Developmental Scale Scores 302 Grade 3 Grade 10

20. Developmental Scale Score (Math) G6 G3 G8 Min Max Raw Score 140 Developmental Scale Scores 298 Grade 3 Grade 10

21. FCAT 2.0/EOC Achievement Level Policy Definitions

22. FCAT 2.0/EOC Achievement Level Policy Definitions FCAT FCAT 2.0 and EOC

23. 2012 FCAT 2.0 Science Equivalent Scale Score(100 - 500) Raw Score Content Scores Equivalent Achievement Level(1 to 5)

24. Geometry and Biology EOC Scores Raw Score Content Scores T-Score Scale(20 to 80) Statewide Comparison by Thirds

25. Statewide Comparison Points Earned by Content Area Test Form Scale Score 1 2 3 Student Name and ID

26. 2012 Geometry EOC Scale Distribution - Statewide

27. 2012 Algebra 1 EOC Scores Raw Score Algebra Content Scores EOC Scale Score(325 - 475) Achievement Level(1 to 5) Source: FL DOE/Office of Assessment

28. 2012 Content Scores

29. Worksheet 1: Calculate the % correct for each content area.

30. Worksheet 1: Calculate the % correct for each content area.

31. Sum Formula and Locking Cell Reference 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 • Cell N1 type formula “=sum(G1:J1)” press ENTER. Result: 45 • Cell O1 type formula “=N1/N1” press ENTER. Result : 1. In Cell O1, click on % sign under Number of the Home Menu. Result: 100% • Highlight Cell N1 and select “Copy” command • Click on Cell N3 then Right click and select Paste Formula. • 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 • 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 be in red.

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

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

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

37. Instructions for Conversion PDF File

38. Update on School Grade

39. Florida School Accountability

40. Adequate Yearly Progress (AYP) Benchmark

41. AYP 39 Components Criteria (NA, No, Yes)

42. Annual Measurable Objectives

43. Elem. & Combination Schools up to grade 7School Grade of 800 Points F: 0-394; D: 395-434; C: 435-494; B: 495-524, A: 525-800

44. 2011-12 through 2013-14 School Grade:Middle School (900 Points) • Algebra EOC – 100 • Participation - 50 • Performance - 50 F: 0-444; D: 445-489; C: 490-559; B: 560-589, A: 590-900

45. 2011-12 High School Grade FCAT Component (800 points) Non-FCAT (800 points) F: 0-789; D: 790-869; C: 870-989; B: 990-1049, A: 1050-1600

46. Annual Learning Gains

47. Annual Learning Gains 2011FCAT Fall Interim Assessment Winter Interim Assessment 2012 Current FCAT School Year Annual Learning Gains Performance Performance Baseline .

48. 3-Ways to Make Annual Learning Gains 49

49. Draft-Annual Learning Gains for FCAT 2.0 Start

50. 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 16DSS points to qualify for 1.1. (12+ 33% more than 12= 16)