Descriptive statistics using Excel This assignment is designed to test Unit Objective 1
Due date: 10am Wednesday week 4 (21 March 2012) or as specified by your lecture
This assignment is marked out of 50 and is worth 5% of the assessment in this unit. You must use Excel to generate the relevant output. The data for this assignment is in the file A1_data.xls which can be found at:
Home page >Assessment information>Assignments>Assignment 1>A1 data.
The file has two worksheets, labelled Data, and Results.
The file is arranged so that all the required results can be presented in the worksheet labelled Results. The appropriate places for graphs, comments and tables are set out in this worksheet, and you are required to hand in a printed copy of the Results worksheet only. It is recommended that you first create the graphs and tables in the data worksheets, and then copy them to the appropriate places in the Results worksheet. In fact on some computers it is essential that you work in this way, as some networked computers have difficulty dealing with data analysis tools when data is selected from a different worksheet.
The assignment is to be handed in as a printout of the completed Results worksheet. Do not print out the data. The Results work sheet has been set up to make printing as easy as possible but it is your responsibility to ensure that all required information actually appears on the pages you hand in. Be sure to include your name and student ID on the worksheet.
While the presentation of the assignment is important, and some marks are designated for presentation, elaborate features are not required. Your work must be easy to read.
For questions involving written comments, the size of the textboxes on the Results worksheet are a general indication of the length of comments required (based on 11-point font.) It is however permissible to make some adjustment to the size and shape of the textboxes, especially when this is necessary to arrange the worksheet for printing.
There are many universities and colleges providing tertiary education in the United States, and news magazines such as US News and World Report track various statistics about these institutions which presumably help students decide which ones to apply to. The data available for this exercise comes from 1995.
There are many state-funded institutions and a larger number of private ones.
From the data provided by US News and World Report, a few columns have been selected and placed in the sheet labeled Data in the file A1_Data.xls.
The columns provide:
? Column A: the name of the institution
? Column B: whether it is private or public, (1 = public, 2 = private)
? Column C: the number of offers of places made by the institution
? Column D: the number of students who enrolled
? Column E: the tuition fees charged (dollars per year)
? Column F: the cost of room and board (dollars per year)
? Column G: the amount per student spent by the institution on instruction
All tables, graphs and comments for this question should be places in the designated spaces in the Worksheet Results.
(a) Complete Table (a). Use Countif or another method such as using the histogram tool to find the frequencies for the number of public and private institutions in the sample and hence complete Table (a).
(b) Display the data in Table (a) as an appropriate pie chart as Graph (b).
(c) Briefly summarise the information about the number of public and private institutions in the sample. Answer in Textbox (c).
(d) Sort all the data by column B (the public/private code). [Please note that if you sort the data incorrectly, it will be hard to achieve any credit for the reminder of the assignment. Some comments on sorting in Excel are given in the Excel tips at the end of this assignment question.] In column I, calculate for each institution the sum of tuition fees and room and board. We will call this variable “Costs”.
Hence complete summary statistics for the Costs variable for the two types of institutions (Table (d)). Give your answers in this table to an appropriate number of decimal places.
(e) Complete the grouped frequency table for the Costs of the two types of institutions in the sample (Table (e)). Find frequency and hence calculate percentage frequency and cumulative percentage frequency for the two types of institutions in the sample.
(f) State the modal class for both the Public and Private institutions. Are these figures very different? Explain why they might be different including in your answer an explanation of what the modal class of a data set represents in Textbox(f).
(g) Construct percentage frequency polygons of the costs variable for the two distributions as one chart as Graph (g).
(h) Discuss the shape of the percentage frequency polygon for the two different types of institutions. Answer in Textbox (h).
(i) List the four measures of variability given in the table. Do public or private institutions show more variability? Answer in Textbox (i).
(j) Private school costs would be expected to be higher than those of public schools. Do the data provided here support this? Quote figures from Table (d) to support your argument. Answer in Textbox (j).
Presentation: 5 marks (If your presentation is easy to read, you will get these 5 marks. Ease of reading is assisted by appropriate font size, borders, colour choice and labelling in graphs, and some care in spelling, grammar and punctuation. )
Sections 1 and 2 of the Unit Guide provide technical information about Excel that you may need in completing this assignment.
Some extra Excel information is given below.
When sorting a number of columns of data by one variable, remember to highlight the whole block of data (all required rows and columns) first. If you just highlight the column for the variable you are sorting by, then the values of this variable will be separated from the cases to which they belong.
Paste special When copying and pasting a table, for example into a different worksheet, it is frequently necessary to use the option: Edit?Paste Special ? Values.
Textbox In order to insert a textbox, first make sure the Draw toolbar is showing (e.g. select “Toolbars” from the View menu, and ensure “Drawing” is checked) and then click on the textbox icon, , click in one corner of where you want the textbox, and drag to the required size.
Percentage frequency polygons When creating the polygons, remember to introduce fictitious empty classes at both ends in the table (rows have been arranged so there is room) so that each polygon touches the X axis at both ends. Use Excel’s “Scatter graph” option (including connecting lines) to create the polygons. Due to axis-labelling issues, it is inappropriate to use Excel’s Line Graph Chart type to produce frequency polygons.
Choosing which columns of a table to graph The series tab in Step 2 of the Chart Wizard provides a convenient way to specify exactly which data you wish to graph. For example, if you highlight all of a table including frequencies and percentage frequencies, and use XY scatter, Excel will graph frequency polygons and percentage frequency polygons all on the same axes. You can use the series tab to remove the frequency polygons.
Page break preview When organizing the arrangement of material to fit neatly onto pages for printing it is sometimes convenient to select “Page Break Preview” from the view menu. You can then vary the number of columns or rows on a page. However, be aware that adding columns or rows is effected by shrinking the content, so this technique should only be used for minor adjustments.
Obtaining Histogram and other Data Analysis tools
See Section 2.5 of the Unit Guide if you have trouble finding Data Analysis on the Tools menu. Also note that the Data Analysis option does not appear on the Tools menu when a graph is highlighted or if the cursor is in a textbox.
Here is a clear version of the question paper: http://188.8.131.52/A1_Data.xls
Here is the data sheet: http://184.108.40.206/A1_Questions.pdf
I need this urgently. If you can answer this send the answers in the excel sheet to firstname.lastname@example.org . And reply here. I’ll give you “Lifesaver” karma points. Thank you