Tables, Graphs and Numerical Summaries of Data Assignment Task 1 In Task 1 you are required to use Excel to produce a frequency Column Chart and a relative frequency Pie-Chart for your sample to show...

Tables, Graphs and Numerical Summaries of Data

Assignment Task 1

In Task 1 you are required to use Excel to produce

a frequency Column Chart and a relative frequency Pie-Chart for your sample to show the number and proportion, respectively, of each building type.


Although the following steps demonstrate how to do this, note that the Excel outputs you obtain will be different to what follows, as you will almost certainly be using different data to that used in this demonstration.



Step 1:


Create a basic frequency table and column chart summary of the

“Building type”

sample data.

Type the word
bins
in a cell in any unused column, say for example, J1.
In range J2:J5 type the individual “values” of the “Building type” categorical variable, V4, i.e.
1
in cell J2,
2
in cell J3,
3
in cell J4 and
4
in cell J5.
From the
Data
menu (Analysis grouping) select
Data Analysis
(see the Tutorial Materials page of the unit website if Data Analysis does not appear under the Analysis grouping on the Data menu).

Select
Histogram
from the
Data Analysis
Tools list and then select
OK.


In the
Histogram
dialogue box you will need to specify, in the
Input Range, the cell locations of the “Building type” variable values i.e. e1:e51, if you wish to include reference to the column heading, “V4”.
You will also need to specify, in the
Bin Range, the cell locations of the individual categorical variable “values” that you identified at the beginning of Step 1 i.e. j1:j5, if you wish to include reference to the column heading, “bins”.
Tick the
Labels
box if the first cells in your Input Range and Bin Range specifications contain column headings (as in this case).
Select
New Worksheet Ply
(if not specified by default) so that the output will appear on a new worksheet of the MS Excel workbook.
Tick the
Chart Output
box in order to request a basic frequency table and column chart.
Select
OK
to obtain the basic frequency table and column chart.



Note:
The frequency table and column chart provided by Excel at this point are very basic and need to be refined so as to provide a more precise and informative summary of the “Building type” data.

Step 2:

Modify the basic frequency table and frequency column chart output provided by Excel to provide a more precise and informative summary of the “Building type” data.
The frequency table should make reference to the variable of interest. We can achieve this, in this case, by typing the (abbreviated) words “Bld. type” over the column heading “bins” in the Excel worksheet.
The categorical “values” 1, 2, 3 and 4 (in this case) in the frequency table should also be changed to the more informative (abbreviated), “Br”, “BrV”, “Wbrd” and “VL” respectively. Note that when you do this, these labels will automatically replace the horizontal axis markings of 1, 2, 3 and 4 on the column chart.
The frequency table contains a row labelled “More” to cover the possibility of “values” in the data set other than 1, 2, 3 and 4. Since there are no such values in this case the “More” row is redundant and can be removed. To do so simply activate the “More” cell and then from the
Home
menu (Cells grouping) access the
Delete
drop down menu and select
Delete Sheet Rows
and the entire row containing the reference to “More” will be deleted from the worksheet. Note that when you do this the marking on the horizontal axis of the column chart specified as “More” will automatically disappear.


The column chart will also require some modification.
Firstly, note that the graph is incorrectly titled “Histogram”. The word “histogram” should in fact be reserved for the equivalent graphical output for quantitative (or (real) numerical) data. If a title is required for this graphical output a more correct and informative description would be, in this case, “Frequency Column Chart for Building Type”. If you left click on the title “Histogram” you will be able to delete the existing title before replacing it with the more suitable title. You may need to adjust the font size (see
Home
menu, Font grouping) to make the title more presentable.
Similarly the label for the horizontal axis, “bins”, should be replaced with a more informative descriptive label for the variable of interest (in this case, say, “Building type”).
The legend on the right hand side of the graph (“Frequency”) is largely redundant as the vertical axis is clearly labelled as such. This legend can be deleted by left clicking on it and then pressing the delete key on your keyboard.
You may observe that the column chart is rather flat and has a rather imprecise scale on the vertical axis. If this is the case these aspects can be rectified by left clicking on the white
Chart Area
around the column chart (within the rectangular border) at which point you should observe small sets of “dot” indicators appear around the perimeter of the border. Clicking on and dragging down the indicators at the centre of the bottom edge of the border will stretch out the column chart vertically and at the same time will increase the graduations along the vertical axis.


Step 3:
Copy the graphical output into a MS Word document to form part of your completed assignment.
With the “dot” indicators still observable around the perimeter of the chart area border (left click on the white
Chart Area
if this is not the case), select the
Copy
() icon from the
Home
menu (Clipboard grouping).
Open your MS Word document and then select the
Paste ()
icon from the
Home
menu (Clipboard grouping) to insert the column chart into the document.
Step 4:

Create a basic (relative frequency) pie chart summary of the “Building type” sample data.

In MS Excel right click anywhere inside the
Chart Area
of the column chart you have just constructed and then select
Change Chart Type
from the pop-up menu.