MAT 114 (09 and 10)



MAT 114: Quantitative Reasoning Tutorial – Microsoft Excel

Nothing from this tutorial is due. The tutorial will guide you through the making and formatting of simple charts. You are encouraged to ‘play’ with the Chart Wizard; as with all Microsoft products, right clicking will often bring up a set of convenient options from which editing can be carried out. The first part of this tutorial follows Section 1B.

Please access Microsoft Excel. The following three problems lead you through the construction of a bar chart, pie chart and multiple line graph.

1. The following data represents economic aid donations per inhabitant of the former G7 countries. Enter the following data in cells A1 through B7.

| | |A |B |C |

| |1 |Canada | 77.87 | |

| |2 |France |104.68 | |

| |3 |Germany | 67.96 | |

| |4 |Italy | 17.20 | |

| |5 |Japan | 69.84 | |

| |6 |UK | 176.06 | |

| |7 |USA | 23.00 | |

Then highlight the information (click and drag) and go to Excel’s Chart Wizard. This is accessed by clicking on Insert

[pic]

And then selecting the type of chart you want.

[pic]

Here we select Column (In Excel, Bar graph is plotted horizontally; column graph is the name for the conventional vertical bar graph.) The first type of column graph, 2D graph, is what we will use, although the picture shows its multiple bar capability. The other choices handle multiple data sets differently and can present the graph 3-dimensionally.

This produces a simple graph with 7 blue bars on a plain background.

The chart will appear on the data sheet highlighted by 8 sets of four squares inside its window. Printing at this point will only print the chart. Clicking outside the chart window removes the highlighting. Printing at this point will print the viewable data sheet and the chart window.

The chart window can be highlighted by simply clicking inside its window. The chart may be cut for pasting into, say, a Microsoft Word document by highlighting, and then copying or cutting (Control-C (copy) or Control-X (cut) or then or .)

It is recommended that you paste all the charts from this tutorial into a single Word (or similar) document – you will be required to do this on the Project.

Now do some reformatting to get the graph to look nicer. In particular, drag on the corners of the graphing window to make this larger (say to fill lines 7 through 37 and columns A through I). Other things to try include.

• Right click where it says Series 1 and then select delete. This label can be useful for multiple bar graphs but serves no purpose here.

• Left-click on the graph itself, then drag on the corner of this to enlarge or shrink the graph within the graphing window.

• Add a title to the chart, or label the axes by Inserting a text box.

• Left-click on a bar of the graph to select the bar – then right click to select then and then “Vary colors by Point.” Or select a color for this particular bar using .then and then “Solid Fill” followed by the choice of color. You can also change the background of the graph – you can add color and even insert a picture. [Warning: The careful use of pictures can enhance the graph; but pictures can also detract from the point being made.] To get the bars to touch, use and then and then sliding the marker to get 0 for the gap width. You will need to do this for histograms (Section 1C).

• The chart window has a box labeled ‘Series 1’ to the right of the bar graph. To remove it, right click in its box and select ‘Clear’.

2. Select the ‘Sheet 2’ tab at the bottom of the Excel sheet that you used for Problem 1. Enter the following data in cells A1 through B6. This information gives the ethnic breakdown of those living in Coconino County and is adapted from

| | |A |B |C |

| |1 |Native American |36,049 | |

| |2 |Asian |1323 | |

| |3 |African American |1739 | |

| |4 |Hawaiian/Pacific Islander |40 | |

| |5 |White |74256 | |

| |6 |Other |7369 | |

Highlight and select pie from the chart menu. Again follow the instructions – initially you may select a 2-dimensuional or 3-dimensional pie chart, and may have the slices touching or set a little apart.

• To change the color of a slice of pie, left click to select the slice and then right click to bring up the menu that lets you format the slice.

• Left click on the chart and then right click to “Add Data Labels”. Right clicking again allows you to “Format Data Labels”

3. Select the ‘Sheet 3’ tab. Enter the following data (which represents the age of age of first marriage of US men and women ) in cells A1 through C11.

| | |A |B |C |D |

| |2 |1900 |25.9 |21.9 | |

| |3 |1910 |25.1 |21.6 | |

| |4 |1920 |24.6 |21.2 | |

| |5 |1930 |24.3 |21.3 | |

| |6 |1940 |24.3 |21.5 | |

| |7 |1950 |22.8 |20.3 | |

| |8 |1960 |22.8 |20.3 | |

| |9 |1970 |23.2 |20.8 | |

| |10 |1980 |24.7 |22.0 | |

| |11 |1990 |26.1 |23.9 | |

| |12 |2000 |26.8 |25.1 | |

Use Excel to draw a multiple bar graph of this data – This is called a column graph in Excel. Then reformat, until the graph looks nice!

The picture will be drawn with the default and will not be good –the year dominates the data. So right click and select to choose Sheet3!$B$1:$C$11 rather than the given =Sheet3!$A$1:$C$11. This removes the bars for the years. You should also edit the horizontal axis labels (same window, right hand side) Enter =Sheet3!$A$1:$A$11 as the Category (X) axis labels. This will then plot the year data along the horizontal axis.

Next, have a change of heart and switch to a multiple line graph. Right click in the chart window and select ‘Change Series Chart Type’ from the menu box that appears and select a line graph. (In fact you can keep one graph as a bar and change the other to a line).

As well as having chart drawing capabilities, Excel can also compute the statistical measures encountered in Section 1B. The part of this tutorial follows Section 1C

4. To get a fourth sheet, click on the new page icon. This will add ‘Sheet 4’ after ‘Sheet 3.’ You can move sheets around or give them names, - perhaps as Problems 1-4 - by right clicking on the tabs.

The following data comes from the US Forest Service.

|ARIZONA |

|Year |Acres Burned |

|1992 |41606 |

|1993 |204774 |

|1994 |222899 |

|1995 |244763 |

|1996 |188187 |

|1997 |18547 |

|1998 |51150 |

|1999 |82280 |

|2000 |82896 |

|2001 |30503 |

|2002 |629876 |

|2003 |188999 |

Input the data from the second into cells A1-A12 of Sheet 4. (There is no need to input the first column.) Computing statistical measures in Excel is done by entering the appropriate formula into any cell and referring to the data cells.

To compute the mean, enter into any cell =average(A1:A12). (The equals is important; there are no spaces.) To compute the median, pick any cell and enter =median(A1:A12). Standard deviation can be found similarly using the format =stdev(A1:A12).

The same idea applies to the 5-number summary. The 5-numbers can be found by typing the necessary question into any cell.

=quartile(A1:A12,0) gives the low value of the data

=quartile(A1:A12,1) gives the lower quartile of the data

=quartile(A1:A12,2) gives the median of the data

=quartile(A1:A12,3) gives the upper quartile of the data

=quartile(A1:A12,4) gives the high value of the data

One word of warning – the lower and upper quartiles are computed differently from the way given in Section 1B and the textbook. Values will be similar but won’t always be the same.

____________________________________________________

One more feature than can be useful when dealing with data is to sort the data. There is a Data tab on the toolbar and then ‘Sort’ lies within a section. Highlight cells A1-A12 and hit this button and the data will be resorted from smallest to largest. A1 will have the low data value; A12 will have the high data value etc. This can be useful when creating histograms as it is easier to count the number of items in each bin once the data has been ordered. [Excel can be used to directly create Histograms but this is a little more difficult and lies beyond the scope of the course.]

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

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

Google Online Preview   Download