How to Create an Excel Graph Depicting a Student’s Rate of ...



How to Create an Excel Graph

Depicting a Student’s Rate of Improvement

for a Whole School Year

(Example: Diego’s Words Per Minute)

Setting Up Your Spreadsheet

• Open Microsoft Excel

• In cell B2, type School Week to represent the weeks of school

• In cell C2, type Benchmark to represent the benchmarks or goals of the skill you are graphing

• In cell D2, type WPM to represent Words Per Minute (or an abbreviation the basic skill you are graphing)

Labeling School Weeks

• In cell B3, type 1 to represent the first week of school

• Continue numbering to 36 in column 2 (e.g., 2, 3, 4 ... 36)

• Finish at cell B38

Entering Benchmarks/Goals

• In cell C3, type the number that represents the fall benchmark (months 1-3) of the skill you are graphing (e.g., 77). This score should be next to school week 1.

• In cell C20, type the number that represents the winter benchmark (months 4-6) of the skill you are graphing (e.g., 92). This score should be next to school week 18.

• In cell C38, type the number that represents the spring benchmark (months 7-10) of the skill you are graphing (e.g., 110). This score should be next school week 36.

Entering Student Scores

• In cells D3 through D38, type the number that represents the score the student achieved during that week of the school year. If a student was not assessed during a certain week of the school year, leave that cell blank.

• Do not enter zero (0) or a score of zero will be calculated into the trend line and interpreted as the student having read zero words correct per minute that week.

Creating a Graph

• Highlight the scores in columns C and D.

• Left click with mouse in cell C2.

• While holding down mouse, highlight columns C and D from row 2 through 38.

• These will be your data points contributing to your graph.

• The selection should include the blank cells in column C.

• Left click “Insert” in the tool bar (typically in the top row) while columns C and D are still highlighted.

• Left click “Chart” in the drop-down menu. A “Chart Wizard” window will appear.

• In the Chart Wizard are two tabs. Make sure you are in the left tab, “Standard Types.”

• Next, you want to left click on the “Line” graph on the left side of your Chart Wizard.

• On the right side is “Chart sub-type.” Choose the graph with the description “Line with markers displayed at each data value.” This option is typically the first graph icon in the second row.

• Left click “Next” at the bottom of your Chart Wizard.

• The upper left tab says “Data Range.” The bottom half of that tab has your data range and series type. You want to select “Columns.”

• The top right tab is labeled “Series.” Left click on “Series.”

• The top half of the “Series” tab has an icon of your graph. The bottom half allows you to label your data for the legend.

Optional

• Left click “Series 1” and enter a name for your benchmark data to the right. Suggestion: Benchmark or Goal.

• Left click on “Series 2” and enter a name for your student’s data to the right. Suggestion: Diego’s Scores or Diego’s WPM.

• Left click “Next” at the bottom of your Chart Wizard.

• The next options include tabs for Titles, Axes, Gridlines, etc.

o If you would like to title your chart, left click on the “Titles” tab. Enter a title (i.e., Diego’s Rate of Improvement) in the first box.

o Enter a description in the Category (X) Axis, which is your number of school weeks. Suggestion: School Week.

o Enter a description in your Value (Y) Axis, which is the number of the skill you are graphing. Suggestion: Words Per Minute or Digits Correct.

• Left click “Next” at the bottom of your Chart Wizard.

Optional

• Click on “Data Labels”

• Click to put a check mark next to “Value”

• Click on the “Next” button

• Note: This can clutter your graph but provides useful information.

• Left click the bottom circle to choose “As object in: …” This will put the graph in the worksheet you have open.

- OR -

• If you prefer to have the graph itself in a worksheet within the Excel document, then choose “As new sheet…” Label the worksheet by clicking on the tab at the bottom of the sheet and typing a name (e.g., Diego’s Graph)

• Left click “Finish” in your Chart Wizard. This will place your graph in your Excel Document.

Resizing the Graph

• You may resize the graph by clicking in any white space which will bring up squares at the corners of your graph. You can put your mouse over these squares and drag the graph to a size you prefer. Keep in mind that a large graph may not print easily.

Coloring the Graph

• Right click in the blank area and a drop down menu appears

• Click on “Format Plot Area”

• The “Format Plot Area” menu appears.

• To eliminate a color border, click next to “None.”

• Click “OK”

Coloring Data Points

• Right click on the data points for which you would like to change the color

• Click on “Format Data Series”

• Choose the foreground, background, and line color

Adding Trendlines

• RIGHT click on any of the student’s scores/data points within your graph.

• Left click “Add Trendline” from the menu that appears.

• Under the “Options” tab, click “Custom” and type in a title for your trendline (e.g., Rate of Improvement).

• Check the box next to “Display equation on chart.”

• Left click “OK.” This will display a trendline on your chart with an equation in the form of y=mx+b. (If you remember your algebra, “m” is the slope!)

• Left clicking on the equation will highlight a “box” around it, and clicking on that box will allow you to move the equation above the chart legend to see it better.

• Repeat this process for the other set of data – your benchmarks. Begin by right-clicking on any of your three goal data points.

• For your benchmark data, label the trendline title “Expected Slope”

• Click next to “Display equation on chart.”

• Click “OK”

Other Graph Information

• The graph depicts the actual scores as data points on the graph -- the number beside the data point indicates the exact score for that week. You can change the size of the font for the numeric values by right-clicking on any of them and choosing a smaller font size. This may reduce some of the clutter on your graph.

• The rate of improvement, or trendline, is calculated using a linear regression, a simple equation of least squares. This line takes into account each score entered to portray an average rate of improvement across school weeks.

• The equation indicates the slope, or rate of improvement. The number, or coefficient, before "x" is the average improvement, which in this case is the average number of words per minute per week gained by the student.

• The slope can change depending on which week (where) you put the benchmark scores on your chart. Enter benchmark scores based on when your school administers their benchmark assessments for the most accurate depiction of expected student progress.

• To add additional progress monitoring/benchmark scores once you’ve already created a graph, enter additional scores next to the corresponding school week. Remember to leave cells blank for the weeks that no score was obtained. The graph will incorporate that score into the set of data points and into the trendline.

Program Excel to Calculate ROI

o Type “ROI” in cell B39 (below your last week of school)

o To calculate the expected slope as per the benchmarks: Click on cell C39

o Put your cursor at the top next to the fx

o Type: =SLOPE(C3:C38,B3:B38)

o Hit Enter/Return

o To calculate the student’s slope: Click on cell D39

o Put your cursor at the top next to the fx

o Type: =SLOPE(D3:D38,B3:B38)

o Hit Enter/Return

[pic]

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

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

Google Online Preview   Download