Graphing With Excel 2003 and 2007



Graphing With Excel 2007

General Excel skills

1. To select the cells to be graphed, click in the middle of the upper left most cell, hold the mouse button, then drag to the bottom right corner of the cells you want. Then let go of the mouse button. All the cells should be shaded slightly, except the first cell, but don’t worry, it has been selected.

2. Data Array is the group of cells that contain the data. It can be selected as described above, or by clicking in the first cell then holding down the control and shift keys while you press the down arrow. An example of a data array is A4:A35. These can also be typed into a formula.

3. Formulas always start with an equal sign. If you aren’t sure of the formatting of a formula, which must be done exactly, then click the fx button on the formula bar at near the top of the screen.

4. Statistics

Mean =AVERAGE(data array)

Median =MEDIAN(data array)

Standard Deviation =STDEV(data array)

Maximum Value =MAX(data array)

Minimum Value =MIN(data array)

1st Quartile =QUARTILE(data array, 1)

3rd Quartile =QUARTILE(data array, 3)

Histograms

To make a histogram for one population, the sequence of steps is to

a. Find the highest and lowest data values.

b. Make a frequency distribution.

c. Make a bar graph of the frequencies.

1. Low and High

To find the lowest value in the data, type the formula =MIN(data array). For example =MIN(B2:B15).

To find the highest value in the data, type the formula = MAX(data array). For example =MAX(B2:B15)

2. Frequency Distribution.

Create lower and upper boundaries. If your classes are (0,20), (20,40), (40,60), (60,80), (80,100) then create your classes as follows: The upper boundary must be slightly less than the next lower boundary because of the way in which Excel will count the data values.

Highlight the empty cells in the frequency column. Move the cursor out of the way without clicking. Type the formula = FREQUENCY( With the mouse, click on B2 and drag down to B15. Type a comma, and then with the mouse click on D2 and drag down to D6 and then type ). Hold the control and shift keys down while you press enter.

3. Graph

• To make the graph, highlight the frequencies you just created.

• Select the Insert drop down menu then select Column graphs.

• Select the first column graph shown.

When you click once on the graph, your tool bars at the top should change. You should see Chart Tools at the top of the screen with three options, Design, Layout and Format.

Select the Design menu, then under Chart Layouts, select

Layout 8. Click on Title, x-axis and y-axis label to edit. The y axis title should be called “frequency”.

To make the x-axis scale appropriate for the data, right click on any of the numbers on the x axis, then select select data. In the dialogue box, under the words Horizontal (Category) Axis Labels, click on the edit button. In the Axis Labels dialogue box, the cursor should be in the space marked Axis label range. Highlight the lower boundaries of your frequency distribution. Then click ok. Then click ok again. To make these numbers shift to the left, click on one of them, select the Home menu item at the top of the computer screen then click the left justify icon.

To put a border around the columns, right click on a bar then select Format Data Series, then select Border Color. Select solid line and choose black as the color.

Scatter Plots

1. To make a scatter plot graph, highlight both data columns. Do not include the headings.

2. Select the Insert drop down menu then select Scatter graphs. Select the first Scatter graph shown. That will produce a graph without titles.

3. Click once on the graph. The tool bar at the top should change. Under Chart Tools at the top of the screen, there are three options, Design, Layout and Format. Select the Design menu and then under Chart Layouts, select Layout 1 to put in titles but not to put in the regression line.

4. Click on each of the three titles to edit.

5. Click on Series 1 and delete.

Select Layout 9 to include the regression line, equation and r2 value.

Box Plots

1. List data in Excel with headers.

2. Sort the values that will be plotted on the graph from least to greates. In this example, sort the visits column. Highlight your data including the headers. Click data and then sort. Make sure my data has headers is checked. In the sort by dropdown box, select data to sort.

3. Next to your data, have excel calculate the following values using the functions below. Be sure to change the values to reflect your data set. B2 represents the first data point in the set and B11 represents the last.

|MIN |65 |MIN(B2:B11) |

|MIN VALID |119.375 |MAX(D1,D3-1.5*(D6-D3)) |

|1ST QUARTILE |167 |QUARTILE(B2:B11,1) |

|AVERAGE(X) |184 |AVERAGE(B2:B11) |

|MEDIAN(M) |186.5 |MEDIAN(B2:B11) |

|3RD QUARTILE |198.75 |QUARTILE(B2:B11,3) |

|MAX VALID |246.375 |MIN(D8,D6+1.5*(D6-D3)) |

|MAX |308 |MAX(B2:B11) |

• Min =MIN(B2:B11)

• Min Valid =MAX(D1,D3-1.5*(D6-D3)) where D1 is the minimum value, D3 is the 1st Quartile, and D6 is the 3rd Quartile

• 1st Quartile =QUARTILE(B2:B11,1)

• Average =AVERAGE(B2:B11)

• Median =MEDIAN(B2:B11)

• 3rd Quartile = QUARTILE(B2:B11,3)

• Max Valid = MIN(D8,D6+1.5*(D6-D3)) where D8 is the maximum value, D6 is the 3rd Quartile, and D3 is the 1st Quartile

• Max = MAX(B2:B11)

|25TH PERCENTILE |167 |

|50TH PERCENTILE |19.5 |

|75TH PERCENTILE |12.25 |

|LOWER LIMIT |47.63 |

|UPPER LIMIT |47.63 |

4. Calculate the width of each box and the lower/higher non-outliers limits.

25th percentile will be equal the 1st Quartile

50th percentile will be equal to Median – 1st Quartile

75th percentile will be equal to 3rd Quartile – Median

Lower Limit will be 1st Quartile – Min Valid

Upper Limit will be Max Valid – 3rd Quartile

5. Select the 25th, 50th, and 75th percentile boxes along with the calculated values.

Insert a new 2-D Stacked Bar graph.

Click Switch Row/Colum (under Chart Tools/Design menu). You should get something like this:

6. Delete the Legend

7. Select the 1st box and set it to be totally transparent.

Right-click on the blue box, select Format Data Series, select Fill, and choose the No fill option.

8. Set the 2nd and 3rd boxes to be transparent with a border. Right click the red box, select Format Data Series, select Fill, and choose the No fill option. Click on Border Color and choose Solid Line. Repeat the same procedure for the green box.

9. Set Vertical axis to cross at maximum value. Right click the Horizontal (Value) Axis, select Format Axis. At the bottom of the formatting screen under Vertical axis crosses, select Maximum axis value

10. Hide the vertical axis bar. Right click the Vertical (Category) Axis. Select Format Axis and choose the Line Color category. Choose No line

11. Now place the label for visits on the graph. Right-click in the chart, Select Data, pick the Horizontal (Category) Axis labels, click the Edit button and pick the cell containing your data label (Visits).

12. To add error bars, Select the first box (transparent), click on Chart Tools, Layout, Select Error bars, More error bars options.

13. Select Minus for the Display Direction.

14. Indicate the Error Amount by clicking Custom. Click the Specify Value button. Leave the Positive Error Value as is and select the Lower limit cell in the spreadsheet for the Negative Error Value. Click OK and close.

15. Click on the 3rd box and complete the same procedure as above but choose the Display Direction Plus and pick the Upper limit cell for the Positive Error Value.

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

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

Google Online Preview   Download