STA1506
ASSIGNMENT 4 2025
DUE: SEPTEMBER 2025 (MEMO)
,STA1506 ASSIGNMENT 4 2025
QUESTION 1
(a) Suppose a Statistics Honors student wants to investigate the performance of a
STA1506 students for his research project. The student entered the data in Microsoft Excel
in column Z with the label "STA1506 Assignment 3 marks", where the second row is the
label and the data starts from row 3. Give the command for calculating the following
statistics using the MS Excel formula.
STA1506 students for his research project. The student entered the data in Microsoft
Excel in column Z with the label "STA1506 Assignment 3 marks", where the second row
is the label and the data starts from row 3. Give the command for calculating the
following statistics using the MS Excel formula.
For data in column Z, rows 3 through 32 (30 values with header in Z2), use Excel’s built-
in functions:
Mean: Use =AVERAGE(Z3:Z32). This returns the arithmetic mean of the values in
Z3:Z32.
Standard deviation (sample): Use =STDEV.S(Z3:Z32). This estimates the sample
standard deviation of Z3:Z32.
First quartile (Q1): Use =QUARTILE.EXC(Z3:Z32,1) or =QUARTILE.INC(Z3:Z32,1).
Excel’s QUARTILE function returns the first quartile when the second argument is 1.
Median: Use =MEDIAN(Z3:Z32). The MEDIAN function returns the middle value of the
dataset.
Coefficient of variation (CV): CV is defined as (standard deviation / mean) × 100. In
Excel, compute as =STDEV.S(Z3:Z32)/AVERAGE(Z3:Z32)*100. This divides the
sample SD by the mean and multiplies by 100 to express CV as a percentage.
(b)(i) Entering and Correcting Data in Excel
Entering data
, Type the 52 marks into an Excel sheet arranged in 4 rows and 13 columns (A1:M4). Put
a header “STA1506 Marks” as the sheet name and ensure A1:M1, A2:M2, etc. each
contain 13 values. The given data (with a leading zero for “03”) is:
Row1: 72, 64, 66, 55, 65, 49, 57, 70, 71, 64, 53, 59, 54
Row2: 82, 74, 46, 78, 75, 03, 91, 60, 41, 74, 53, 169, 84
Row3: 62, 84, 86, 85, 95, 89, 87, 90, 91, 94, 93, 69, 94
Row4: 92, 84, 26, 85, 85, 91, 177, 40, …, 64, 53, 49, 74
In Excel, you can enter “03” as 3 (Excel will drop the leading zero).
Naming the sheet: Double-click the sheet tab and rename it STA1506 Marks.
Correcting errors: Locate the cells with incorrect entries. Change 169 to 19 and 177 to
77 by simply editing those cells. (For example, select the cell containing 169 and type
19 over it.)
Filling missing value: The blank (“…”) in Row4 should be replaced with 51. Simply enter
51 into that empty cell.
Thus the final data block is complete and corrected.
(b)(ii) Descriptive Statistics via Excel Formulas
Using Excel formula bar on the entered data (all 52 values), compute the following
(assuming the data range is A1:M4 or a single block):
Mean: =AVERAGE(A1:M4). Computes average of all values.
Mode: =MODE.SNGL(A1:M4). Returns the most frequent value in the range. (If multiple
modes, Excel’s MODE.SNGL gives the first one.)
Median: =MEDIAN(A1:M4). Middle value of the dataset.
Range: Compute as =MAX(A1:M4) - MIN(A1:M4).
Standard Deviation (sample): =STDEV.S(A1:M4).
Coefficient of Variation: =STDEV.S(A1:M4)/AVERAGE(A1:M4)*100. (As above, this
yields percentage form.)
Lower (First) Quartile (Q1): =QUARTILE.EXC(A1:M4,1) (or QUARTILE.INC).
ASSIGNMENT 4 2025
DUE: SEPTEMBER 2025 (MEMO)
,STA1506 ASSIGNMENT 4 2025
QUESTION 1
(a) Suppose a Statistics Honors student wants to investigate the performance of a
STA1506 students for his research project. The student entered the data in Microsoft Excel
in column Z with the label "STA1506 Assignment 3 marks", where the second row is the
label and the data starts from row 3. Give the command for calculating the following
statistics using the MS Excel formula.
STA1506 students for his research project. The student entered the data in Microsoft
Excel in column Z with the label "STA1506 Assignment 3 marks", where the second row
is the label and the data starts from row 3. Give the command for calculating the
following statistics using the MS Excel formula.
For data in column Z, rows 3 through 32 (30 values with header in Z2), use Excel’s built-
in functions:
Mean: Use =AVERAGE(Z3:Z32). This returns the arithmetic mean of the values in
Z3:Z32.
Standard deviation (sample): Use =STDEV.S(Z3:Z32). This estimates the sample
standard deviation of Z3:Z32.
First quartile (Q1): Use =QUARTILE.EXC(Z3:Z32,1) or =QUARTILE.INC(Z3:Z32,1).
Excel’s QUARTILE function returns the first quartile when the second argument is 1.
Median: Use =MEDIAN(Z3:Z32). The MEDIAN function returns the middle value of the
dataset.
Coefficient of variation (CV): CV is defined as (standard deviation / mean) × 100. In
Excel, compute as =STDEV.S(Z3:Z32)/AVERAGE(Z3:Z32)*100. This divides the
sample SD by the mean and multiplies by 100 to express CV as a percentage.
(b)(i) Entering and Correcting Data in Excel
Entering data
, Type the 52 marks into an Excel sheet arranged in 4 rows and 13 columns (A1:M4). Put
a header “STA1506 Marks” as the sheet name and ensure A1:M1, A2:M2, etc. each
contain 13 values. The given data (with a leading zero for “03”) is:
Row1: 72, 64, 66, 55, 65, 49, 57, 70, 71, 64, 53, 59, 54
Row2: 82, 74, 46, 78, 75, 03, 91, 60, 41, 74, 53, 169, 84
Row3: 62, 84, 86, 85, 95, 89, 87, 90, 91, 94, 93, 69, 94
Row4: 92, 84, 26, 85, 85, 91, 177, 40, …, 64, 53, 49, 74
In Excel, you can enter “03” as 3 (Excel will drop the leading zero).
Naming the sheet: Double-click the sheet tab and rename it STA1506 Marks.
Correcting errors: Locate the cells with incorrect entries. Change 169 to 19 and 177 to
77 by simply editing those cells. (For example, select the cell containing 169 and type
19 over it.)
Filling missing value: The blank (“…”) in Row4 should be replaced with 51. Simply enter
51 into that empty cell.
Thus the final data block is complete and corrected.
(b)(ii) Descriptive Statistics via Excel Formulas
Using Excel formula bar on the entered data (all 52 values), compute the following
(assuming the data range is A1:M4 or a single block):
Mean: =AVERAGE(A1:M4). Computes average of all values.
Mode: =MODE.SNGL(A1:M4). Returns the most frequent value in the range. (If multiple
modes, Excel’s MODE.SNGL gives the first one.)
Median: =MEDIAN(A1:M4). Middle value of the dataset.
Range: Compute as =MAX(A1:M4) - MIN(A1:M4).
Standard Deviation (sample): =STDEV.S(A1:M4).
Coefficient of Variation: =STDEV.S(A1:M4)/AVERAGE(A1:M4)*100. (As above, this
yields percentage form.)
Lower (First) Quartile (Q1): =QUARTILE.EXC(A1:M4,1) (or QUARTILE.INC).
