Group Exercise #1 – Construct a frequency distribution
Group Exercise #1
Based upon your data set, complete the following directly using the Excel file (Include the Excel function used for the calculation so that your work could be traced). Use a new worksheet for each task. Submit your work (in an Excel file) to Canvas.
Task 1
a) Construct a frequency distribution table (including columns “frequency”, “relative frequency”, and “percent frequency”) to summarize the variable “Branch”, and based upon the frequency table to construct a pie chart for this variable. What observations could be obtained from the pie chart?
b) Construct a single bar chart that shows the number of male and female customers, respectively, at each of the four branches. What observations could be obtained from the bar chart?
c) Construct a histogram for the variable “Income”. Comment on the shape of the histogram.
Task 2 Consider the variable “Income”:
a) Find the mean, the median, the range, and the sample standard deviation.
b) Compute the first quartile and the third quartile.
c) Give the five-number summary.
d) Use the IQR rule to determine whether there are any outliers.
** Ensure you are working with the correct dataset (No mark will be given for using a wrong dataset). ** List out all the names of the members who have participated in this exercise. (All participated students in the same group will receive the same mark)
For those of you who want some assistance in using Excel Demonstration in using the formula and constructing graphs in Excel: [Please note that my version of Excel may be different from yours, and hence, the formulas or the icons presented in this section could be different] An example with sample size 15:
Count the frequency of “gender”: The data to be included are from cell C2 to C16 in the above Excel example: – Count the number of male in the dataset = COUNTIF(C2:C16,”M”) – Count the number of male in the dataset = COUNTIF(C2:C16,”F”)
From that, you can construct the frequency distribution table:
Construct a Pie Chart:
Construct a Bar Chart:
(A combined Bar chart showing the number of male and female at each branch) First, for each branch, count the number of males (M) and females (F) and construct a table so that we could select the frequencies to construct a bar chart.
Construct a Histogram:
(Construct a histogram for “Age”)
Usually, you may need to adjust the histogram with different values of bars/classes.
In the Excel, it initially gives only two classes. Adjustment is needed.
By trial-and-error in determining the number bars/classes, choose the one that is most appropriate for the dataset.
Some more Excel functions:
Mean = average(“data range”) [for example: = average(D2:D16)] Median = median(“data range”) [for example: = median(D2:D16)] Range = max(“data range”) – min(“data range”)
[for example: = max(D2:D16) – min(D2:D16)]
Standard Deviation = stdev.s(“data range”) [for example: = stdev.s(D2:D16)] First Quartile (25th percentile) = quartile.exc(“data range”, 1) or = percentile.exc(“data range”, 0.25)
[for example: = quartile.exc (D2:D16,1) or = percentile.exc (D2:D16,0.25)]
Third Quartile (75th percentile) = quartile.exc(“data range”, 3) or = percentile.exc(“data range”, 0.75)
[for example: = quartile.exc (D2:D16,3) or = percentile.exc (D2:D16,0.75)]

Having Trouble Meeting Your Deadline?
Get your assignment on Group Exercise #1 – Construct a frequency distribution completed on time. avoid delay and – ORDER NOW