STAV102: PRACTICAL 1
Objectives
1. Describe data graphically.
2. Use descriptive techniques to answer problems.
Instructions
Logon to the computer and download the file [Link] and download the file
from the STAE/STAV102 Moodle Site. Save this file onto your network drive (H:\), your
flash drive or your OneDrive folder. Open Microsoft Excel and open the
[Link] file. You are now ready to complete the next section of the practical.
Method
Construct a Pie Chart and Simple Bar Graph
With the threat of obesity being well documented and a growing concern, a
researcher interviewed 100 adults. In the interview the researcher asked what
activities (if any) the respondents used to lose weight or stay in shape. The possible
responses were “Diet”, “Exercise”, “Diet & Exercise” and “None”. The responses are
given in the Sheet labelled “Health Activity”, open this sheet now. The ID column can
be ignored (it identifies the respondent). We will now construct a frequency table to
summarise the data.
• Click on cell E2 and type in “Category”. In cell F2 type “Frequency”. You can make these
bold.
• In the cells under “Category” type in the responses listed above (be sure to type them in
exactly as they appear). You should now have the following:
• To count the frequency of each response the COUNTIF() function is used. Click on cell
F3. In this cell we wish to indicate the frequency of the response “Diet”.
o Click on , select the Stastical category, find and click on the COUNTIF
function and then click OK.
1
o You should see:
For the Range option you will indicate the cells which contain the individual
responses. For the Criteria option you will select the relevant category value
in your frequency table.
o Click next to the Range field and select all 100 responses in Column B
(B2:B101). Click .
o Click next to the Criteria field and select the category value “Diet” in you
frequency table (E3). Click and then click OK.
o The value 15 should appear as the frequency value in cell F3. This means that
15 of the 100 respondents indicated that they use diet to control their weight.
• Repeat the above steps to find the frequencies for the “Exercise”, “Diet & Exercise” and
“None” categories. When you are finished you should see the following table:
• Now select all the cells in this frequency table (E3:F6). Click on the Insert tab and click
on . Select the option under the 2-D Pie list. The following Pie Chart should
appear:
You now know how to construct a pie chart in Microsoft Excel. From the pie chart: Which
response was the most frequent? Which was the least?
2
You can create a simple bar graph using the exact same method, however instead of
selecting on the Insert tab, click . Now click under the 2-D Column list to
bring up a bar graph. Do this now.
You have created a pie chart and a bar graph from raw data.
Construct a Multiple or Stacked Bar Graph
In an effort to understand the relationship between faculty members and students at a
university, a researcher asked a random sample of both undergraduate (UG) and post-
graduate (PG) students whether they found their lecturers/supervisors friendly and
approachable. The possible responses to this question were “Yes”, “Undecided” and “No”.
The survey included a total of 280 students and the results of the survey are given and
tabulated in the “Lecturers” sheet in the Excel document. You should see the following
summary table:
The COUNTIF function was used to populate this table. Investigate how this was done by
clicking on each of the values in the table and checking the formula used (in the forumula
bar next to ). Be sure that you understand how these values were found. Use the
Code/Interpretation table in the sheet to help you (if needed).
Now use what was learnt in Practical 0 to compute the Row and Column Totals for this table.
(Row Totals in cells H5 and H6, Column Totals in cells E7, F7 and G7).
To construct a multiple bar graph from the table:
• Select the entire table (excluding the totals) (i.e. click on cell C3 and drag to cell G6).
• Now click on the Insert tab and click .
• Now click under the 2-D Column list to bring up a bar chart. You should see the
following chart:
3
If the researcher asked you to interpret the results given in this chart, what would you be
able to tell him/her?
A stacked bar graph can be constructed in by following same method, however for a stacked
bar graph select under the 2-D Column list. Do this now.
You have created a multiple and a stacked bar graph from a frequency table.
Construct a Histogram
A lecturer gives a class-test to a sample of n = 20 students. On the test, the students also
indicate how many hours they studied during the past week. The lecturer records the hours
studied and the test score for each student and would like to make various conclusions
about her students in general. The data recorded is given in the sheet labelled “Test (Hrs
and Scores)”. Go to this sheet now.
To run many of the statistical analysis required for the remainder of the course, it is
necessary to load the data analysis tool pack. To do this, follow the instructions below:
• Click the File tab and select Options.
• Select Add-Ins on the left menu and make sure that the Manage: option at the bottom
of the right hand pane is set to Excel Add-ins. Click Go … .
• Select the Analysis ToolPak and the Analysis ToolPak – VBA options and click OK.
You have now installed software necessary for statistical analysis.
4
Construct a histogram
• Click the Data tab and select Data Analysis, Histogram and click OK.
• You will see the following menu:
• Specify the input range by dragging your mouse over cells C1 to C21. (Hint: Use to
clear screen view). Ignore “Bin range”.
• Click the Labels box. For this practical, select New Worksheet Ply and Type “Histogram
(Test Scores)” in the box next to it.
• Select Chart Output.
• Click OK.
• Double click on one of the bars in your chart. A menu will appear which allows you to
change the Gap Width. Set the gap width to 0% (No gap).
You have now charted the score data as a Histogram.
The output (results of the analysis) now appears as a new sheet named “Histogram (Test
Scores)” (which you specified above).
You should see that the histogram is negatively skewed. Did you see this?
Once you have completed these exercises you can continue to the questions which
follow.
The questions which follow use the [Link] file found on the Moodle site. Find
this file and open it now.
5
STAV102: Practical Assignment 1
Instructions:
Complete the questions below and submit your solutions on the module Moodle site by
clicking the “Practical 1 Submission” link in the “Section 2: Organise and Describe
Data” block.
Question 1
The local travel agency offers four types of holiday packages; “Beach”, “Cruise”, “European”
and “Safari”. For marketing purposes, the agency now wishes to determine which of their
holiday packages is most appealing to their clientele. To do this a random sample of 240 of
their clients was taken and each clients preferred holiday package was recorded. The
results are given in the sheet titled Q1 in the [Link] file.
Using the given data, construct a frequency table for the travel agency data:
Category Frequency
Which is the most popular holiday package offered by the travel agency?
Which is the least popular holiday package offered by the travel agency?
Construct a pie graph in Excel.
(Select the option on the Moodle site which best represents/replicates your graph)
1
Question 2
The use of internet banking in the current day and age is very popular and provides the
general population with access to their financial information at any time of the day. To
investigate South African’s perception internet banking and how this changes over time,
three random samples (each of size 100) were taken. The first sample was taken in in 2006,
the second in 2011 and the third in 2016. Each respondent was asked whether they would
use/do use internet banking facilities. The responses were limited to; “Yes”, “Undecided”
and “No”. The results are given in the sheet titled Q2 in the [Link] file.
Using the given response data, complete the following frequency table:
Year
2006 2011 2016
Yes
Response
Undecided
No
Construct a multiple bar graph of the data and add a legend
(Select the option on the Moodle site which best represents/replicates your graph)
Using your graph, comment on the change in the perception of South Africans to the use of
internet banking services in the time period observed.
(Select the option on the Moodle site which best represents/replicates your answer)
2
Question 3
A lecturer takes a random sample of practical marks and a random sample of test marks
from the STAE101 course for technical analysis. This data is given in the sheet titled Q3 in
the [Link] file.
Plot separate histograms for the practical and test marks. Use these histograms to answer
the following.
Describe the shape of the histogram of the practical marks.
Describe the shape of the histogram of the test marks.
Using the histogram of the test marks, estimate the pass rate for the test.
Which of the histograms (practical/test) indicate better overall performance by the students?
(Select the option on the Moodle site which best represents/replicates your answer)