Programming Microsoft Excel to Graph Rate of Improvement ...



Programming Microsoft Excel to Graph Rate of Improvement: Fall to Winter

*Open Microsoft Excel*

Setting Up Your Spreadsheet

In cell A1, type 3rd Grade ORF

In cell A2, type First Semester

In cell A3, type School Week

In cell A4, type Benchmark

In cell A5, type the Student’s Name (Swiper Example)

Labeling School Weeks

Starting with cell B3, type numbers 1 through 18 going across row 3 (horizontal).

Numbers 1 through 18 represent the number of the school week.

You will end with week 18 in cell S3.

Note: You may choose to enter the date of that school week across row 2 to easily identify the school week.

Entering Benchmarks (3rd Grade ORF)

In cell B4, type 77. This is your fall benchmark.

In cell S4, type 92. This is your winter benchmark.

Entering Student Data (Sample)

Enter the following numbers, going across row 5, under corresponding week numbers.

Week 1 – 41

Week 8 – 62

Week 9 – 63

Week 10 – 75

Week 11 – 64

Week 12 – 80

Week 13 – 83

Week 14 – 83

Week 15 – 56

Week 17 – 104

Week 18 – 74

Graphing the Data

Highlight cells A4 and A5 through S4 and S5

Follow Excel 2003 or Excel 2007 directions from here

(Excel 2003)

Across the top of your worksheet, click on “Insert”

In that drop-down menu, click on “Chart”

Your chart wizard will appear.

Click on “Line”

Make sure the icon is highlighted with the description that reads “Line with markers displayed at each data value”

Click “Next”

Make sure there you have a mark next to “Rows”

Click “Next”

You can add a Chart Title, and label your X-Axis (School Weeks) and Y-Axis (WPM)

Click “Next”

Decide if you want your graph as an “object in” or “as new sheet”

Click “Finish”

Your graph will appear

(Excel 2007)

Click “Insert”

Find the icon for “Line”

Click the arrow below “Line” which will open a drop-down menu of 6 graphics

Click on “Line with markers”

Your graph appears within your spreadsheet

Graphing the Trendline

On your graph, right click on any one student data point

A drop-down menu will appear

Click on “Add Trendline”

Click on or mark “Linear”

Follow Excel 2003 or 2007 versions

(Excel 2003)

There are two tabs at the top of your wizard, click on “Options”

Check the box next to “Custom”

Then type in a label for your trendline such as Rate of Improvement

Below that section, check the box next to “Display equation on chart”

(Excel 2007)

In the section below the trendline type, mark next to “Custom”

Then type in a label for your trendline such as Rate of Improvement

Below that section, mark next to “Display equation on chart”

...for both versions

Clicking on the equation will highlight a box border around the equation

Clicking on the edge of that border allows you to drag the equation to a different place on the graph so you can see it better

Graphing Expected Rate of Improvement

On your graph, right click on one of the benchmark data points

Click on “Add Trendline” and follow the same steps as before except label your new line Expected Rate of Improvement

Calculating Needed RoI

In cell T3, type Needed RoI

Click on cell T5

In the fx line (at top of sheet) type this formula =((S4-B5)/18)

Then hit enter

Your result should read: 2.83333...

This formula simply subtracts the student’s actual beginning of year (BOY) benchmark from the expected middle of year (MOY) benchmark, then dividing by 18 for the first 18 weeks (1st semester).

Calculating Actual RoI - Benchmark

In cell U3, type Actual RoI

Click on cell U4

In the fx line (at top of sheet) type this formula =SLOPE(B4:S4,B3:S3)

Then hit enter

Your result should read: 0.8825...

This formula considers 18 weeks of benchmark data and provides an average growth or change per week.

Calculating Actual RoI - Student

Click on cell U5

In the fx line (at top of sheet) type this formula =SLOPE(B5:S5,B3:S3)

Then hit enter

Your result should read: 2.5137...

This formula considers 18 weeks of student data and provides an average growth or change per week.

Programming Microsoft Excel to Graph Rate of Improvement: Winter to Spring

*Click on new worksheet*

Setting Up Your Spreadsheet

In cell A1, type 3rd Grade ORF

In cell A2, type Second Semester

In cell A3, type School Week

In cell A4, type Benchmark

In cell A5, type the Student’s Name (Swiper Example)

Labeling School Weeks

Starting with cell B3, type numbers 1 through 18 going across row 3 (horizontal).

Numbers 1 through 18 represent the number of the school week.

You will end with week 18 in cell S3.

Note: You may choose to enter the date of that school week across row 2 to easily identify the school week.

Entering Benchmarks (3rd Grade ORF)

In cell B4, type 92. This is your winter benchmark.

In cell S4, type 110. This is your spring benchmark.

Entering Student Data (Sample)

Enter the following numbers, going across row 5, under corresponding week numbers.

Week 1 – 74

Week 3 – 85

Week 4 – 89

Week 5 – 69

Week 6 – 85

Week 7 – 96

Week 8 – 90

Week 9 – 84

Week 10 – 106

Week 11 – 94

Week 15 – 100

Graphing the Data

Highlight cells A4 and A5 through S4 and S5

Follow Excel 2003 or Excel 2007 directions from here

(Excel 2003)

Across the top of your worksheet, click on “Insert”

In that drop-down menu, click on “Chart”

Your chart wizard will appear.

Click on “Line”

Make sure the icon is highlighted with the description that reads “Line with markers displayed at each data value”

Click “Next”

Make sure there you have a mark next to “Rows”

Click “Next”

You can add a Chart Title, and label your X-Axis (School Weeks) and Y-Axis (WPM)

Click “Next”

Decide if you want your graph as an “object in” or “as new sheet”

Click “Finish”

Your graph will appear

(Excel 2007)

Click “Insert”

Find the icon for “Line”

Click the arrow below “Line” which will open a drop-down menu of 6 graphics

Click on “Line with markers”

Your graph appears within your spreadsheet

Graphing the Trendline

On your graph, right click on any one student data point

A drop-down menu will appear

Click on “Add Trendline”

Click on or mark “Linear”

Follow Excel 2003 or 2007 versions

(Excel 2003)

There are two tabs at the top of your wizard, click on “Options”

Check the box next to “Custom”

Then type in a label for your trendline such as Rate of Improvement

Below that section, check the box next to “Display equation on chart”

(Excel 2007)

In the section below the trendline type, mark next to “Custom”

Then type in a label for your trendline such as Rate of Improvement

Below that section, mark next to “Display equation on chart”

...for both versions

Clicking on the equation will highlight a box border around the equation

Clicking on the edge of that border allows you to drag the equation to a different place on the graph so you can see it better

Graphing Expected Rate of Improvement

On your graph, right click on one of the benchmark data points

Click on “Add Trendline” and follow the same steps as before except label your new line Expected Rate of Improvement

Calculating Needed RoI

In cell T3, type Needed RoI

Click on cell cell T5

In the fx line (at top of sheet) type this formula =((S4-B5)/18)

Then hit enter.

Your result should read: 2

This formula simply subtracts the student’s actual middle of year (MOY) benchmark from the expected end of year (EOY) benchmark, then dividing by 18 for the first 18 weeks (2nd semester).

Calculating Actual RoI - Benchmark

In cell U3, type Actual RoI

Click on cell U4

In the fx line (at top of sheet) type this formula =SLOPE(B4:S4,B3:S3)

Then hit enter

Your result should read: 1.06

This formula considers 18 weeks of benchmark data and provides an average growth or change per week.

Calculating Actual RoI - Student

Click on cell U5

In the fx line (at top of sheet) type this formula =SLOPE(B5:S5,B3:S3)

Then hit enter

Your result should read: 1.89

This formula considers 18 weeks of student data and provides an average growth or change per week.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download