Skip to the content
How to use this manual: In the lab, copy the blank working table at the start of the Calculation into your record book, type the listed Excel formula (or ToolPak menu path) into the cell, and write the value Excel returns into the Result column. The Calculation section shows the completed table with the values, and the Result states the decision or interpretation.

List of Practical Experiments (Official Syllabus)

  1. Central Tendency — calculate mean, median, mode for a given dataset.
  2. Dispersion — compute variance, standard deviation, coefficient of variation.
  3. Skewness and Kurtosis — interpret the distribution shape.
  4. Correlation Analysis — simple correlation coefficient and correlation matrix for multiple variables (height, weight, age).
  5. Simple Linear Regression — fit a regression line and estimate the dependent variable.
  6. Z and t-tests — one-sample and two-sample tests (large & small samples).
  7. Paired t-test — before-and-after comparison.
  8. F-test — equality of variances between two independent samples.
  9. Chi-Square Test — goodness of fit.
  10. ANOVA — one-way and two-way to test differences among groups.

Experiment 1 — Central Tendency

1. Problem

Marks of 12 students are entered in cells A2:A13: 45, 52, 38, 71, 64, 49, 58, 33, 67, 72, 55, 49. Compute the mean, median, mode, geometric mean and harmonic mean.

2. Aim

To compute the different measures of central tendency in MS-Excel using built-in functions.

3. Formula

\[ \bar x = \frac{\sum x}{n}, \qquad GM = \Big(\textstyle\prod x_i\Big)^{1/n}, \qquad HM = \frac{n}{\sum (1/x_i)} \]

Applying it:

  1. Enter the data in A2:A13.
  2. Type each function in column C as shown.
  3. Record the value Excel returns.

4. Calculation

Blank working table:

CellFormulaResult
C2=AVERAGE(A2:A13)
C3=MEDIAN(A2:A13)
C4=MODE.SNGL(A2:A13)
C5=GEOMEAN(A2:A13)
C6=HARMEAN(A2:A13)
CellFormulaResult
C2=AVERAGE(A2:A13)54.42
C3=MEDIAN(A2:A13)53.5
C4=MODE.SNGL(A2:A13)49
C5=GEOMEAN(A2:A13)53.01
C6=HARMEAN(A2:A13)51.54

5. Result

Mean = 54.42, median = 53.5, mode = 49 (appears twice). As expected AM (54.42) > GM (53.01) > HM (51.54).

Experiment 2 — Dispersion

1. Problem

Using the same 12 marks (A2:A13), compute the range, sample variance, sample standard deviation, coefficient of variation and the quartiles.

2. Aim

To measure the spread of the data using Excel's dispersion functions.

3. Formula

\[ s^2 = \frac{\sum(x-\bar x)^2}{n-1}, \qquad CV = \frac{s}{\bar x}\times 100, \qquad IQR = Q_3 - Q_1 \]

Applying it:

  1. Use MAX/MIN for the range, VAR.S/STDEV.S for sample variance/SD.
  2. CV \(=\) (SD / mean) × 100.
  3. Use QUARTILE.INC for Q1 and Q3; IQR = Q3 − Q1.

4. Calculation

Blank working table:

StatisticFormulaResult
Range=MAX(A2:A13)-MIN(A2:A13)
Variance (sample)=VAR.S(A2:A13)
SD (sample)=STDEV.S(A2:A13)
CV (%)=STDEV.S(A2:A13)/AVERAGE(A2:A13)*100
Q1=QUARTILE.INC(A2:A13,1)
Q3=QUARTILE.INC(A2:A13,3)
IQR=QUARTILE.INC(A2:A13,3)-QUARTILE.INC(A2:A13,1)
StatisticFormulaResult
Range=MAX(A2:A13)-MIN(A2:A13)39
Variance (sample)=VAR.S(A2:A13)157.17
SD (sample)=STDEV.S(A2:A13)12.54
CV (%)=STDEV.S(A2:A13)/AVERAGE(A2:A13)*10023.04
Q1=QUARTILE.INC(A2:A13,1)48.0
Q3=QUARTILE.INC(A2:A13,3)64.75
IQR=QUARTILE.INC(A2:A13,3)-QUARTILE.INC(A2:A13,1)16.75

5. Result

The data have range 39, SD 12.54 and CV 23.04 % (moderate relative variability); the middle 50 % of marks span an interquartile range of 16.75 (from 48.0 to 64.75).

Experiment 3 — Skewness and Kurtosis

1. Problem

For the same 12 marks, compute the skewness and kurtosis and interpret the shape of the distribution.

2. Aim

To measure the asymmetry (skewness) and peakedness (kurtosis) of the data using Excel.

3. Formula

\[ \text{SKEW} = \frac{n}{(n-1)(n-2)}\sum\left(\frac{x-\bar x}{s}\right)^3, \qquad \text{KURT (excess)} = 0 \text{ for a normal distribution} \]

Applying it:

  1. Use =SKEW(A2:A13) for the (sample) coefficient of skewness.
  2. Use =KURT(A2:A13) for the excess kurtosis (0 for a normal curve).
  3. Optionally run Data → Data Analysis → Descriptive Statistics for a full report.

4. Calculation

Blank working table:

FormulaResultInterpretation
=SKEW(A2:A13)
=KURT(A2:A13)
FormulaResultInterpretation
=SKEW(A2:A13)−0.14slightly negatively skewed (nearly symmetric)
=KURT(A2:A13)−0.86negative excess ⇒ platykurtic (flatter than normal)

5. Result

Skewness = −0.14 (close to 0), so the distribution is nearly symmetric with a very slight left tail; kurtosis = −0.86 (< 0), so it is platykurtic — flatter and broader than a normal curve.

Experiment 4 — Correlation Analysis & Matrix

1. Problem

For 10 individuals, Height (cm) is in A2:A11, Weight (kg) in B2:B11 and Age (yrs) in C2:C11. Compute the pairwise correlations and the full correlation matrix.

Height150152155158160163165168170175
Weight50525458596264677074
Age22252128243026273229

2. Aim

To compute Pearson correlation coefficients between pairs of variables and assemble the correlation matrix using the Analysis ToolPak.

3. Formula

\[ r = \frac{\sum(x-\bar x)(y-\bar y)}{\sqrt{\sum(x-\bar x)^2\,\sum(y-\bar y)^2}} \]

Applying it:

  1. Use =CORREL(range1, range2) for each pair.
  2. For the matrix: Data → Data Analysis → Correlation, input A1:C11 with labels ticked.
  3. Interpret each coefficient (sign and strength).

4. Calculation

Blank correlation matrix:

HeightWeightAge
Height1
Weight1
Age1

Pairwise: =CORREL(A2:A11,B2:B11) → 0.998 (Height–Weight); =CORREL(A2:A11,C2:C11) → 0.735 (Height–Age); =CORREL(B2:B11,C2:C11) → 0.760 (Weight–Age).

HeightWeightAge
Height1
Weight0.9981
Age0.7350.7601

5. Result

Height and Weight are almost perfectly correlated (0.998); Age is moderately positively correlated with both (0.74 and 0.76). All relationships are positive.

Experiment 5 — Simple Linear Regression

1. Problem

Study hours are in A2:A11 = 2, 3, …, 11 and the corresponding marks in B2:B11 = 50, 55, 62, 66, 72, 75, 80, 85, 90, 95. Fit the regression of marks on hours and predict the marks for 8.5 hours.

2. Aim

To fit a simple linear regression line \(\hat Y = a + bX\), assess its fit \((R^2)\), and forecast.

3. Formula

\[ b = \frac{\sum(x-\bar x)(y-\bar y)}{\sum(x-\bar x)^2}, \qquad a = \bar y - b\bar x, \qquad \hat Y = a + bX \]

Applying it:

  1. =SLOPE(B2:B11,A2:A11) and =INTERCEPT(B2:B11,A2:A11) for \(b\) and \(a\).
  2. =RSQ(B2:B11,A2:A11) for the coefficient of determination.
  3. =FORECAST.LINEAR(8.5,B2:B11,A2:A11) to predict.
  4. For a full report: Data → Data Analysis → Regression (Input Y = B, Input X = A).

4. Calculation

Blank working table:

QuantityFormulaResult
Slope \(b\)=SLOPE(B2:B11,A2:A11)
Intercept \(a\)=INTERCEPT(B2:B11,A2:A11)
\(R^2\)=RSQ(B2:B11,A2:A11)
Predict at 8.5=FORECAST.LINEAR(8.5,B2:B11,A2:A11)
QuantityFormulaResult
Slope \(b\)=SLOPE(B2:B11,A2:A11)4.909
Intercept \(a\)=INTERCEPT(B2:B11,A2:A11)41.09
\(R^2\)=RSQ(B2:B11,A2:A11)0.997
Predict at 8.5=FORECAST.LINEAR(8.5,B2:B11,A2:A11)82.82

Regression line: \(\hat Y = 41.09 + 4.909\,X\). The ToolPak report gives \(R^2 = 0.997\), \(F \approx 2734\), \(p \approx 0\).

5. Result

Marks rise by about 4.91 per extra study hour; the model explains 99.7 % of the variation and is highly significant. Predicted marks at 8.5 hours ≈ 82.82.

Experiment 6 — Z and t-Tests (One & Two Sample)

1. Problem

(a) 100 students have mean IQ 105 with σ = 15; test \(H_0:\mu = 100\). (b) A sample of 9 items has mean 47.5 and sample SD 4; test \(H_0:\mu = 50\) at 5 %. (c) Compare two independent methods (A in A2:A11, B in B2:B13) by a two-sample t-test.

2. Aim

To carry out one-sample large-sample (Z) and small-sample (t) tests and a two-sample t-test in Excel.

3. Formula

\[ Z = \frac{\bar x-\mu_0}{\sigma/\sqrt n}, \qquad t = \frac{\bar x-\mu_0}{s/\sqrt n} \]

Applying it:

  1. One-sample Z: \(Z = (\bar x-\mu_0)/(\sigma/\sqrt n)\); two-tailed =2*(1-NORM.S.DIST(ABS(Z),TRUE)).
  2. One-sample t: \(t = (\bar x-\mu_0)/(s/\sqrt n)\); two-tailed =T.DIST.2T(ABS(t),df).
  3. Two-sample t: =T.TEST(A2:A11,B2:B13,2,2) (2 = two-tailed, 2 = equal variances), or ToolPak t-Test: Two-Sample Assuming Equal Variances.

4. Calculation

Blank working table:

TestStatisticTwo-tailed pDecision
One-sample Z
One-sample t (df = 8)

(a) \(Z = (105-100)/(15/\sqrt{100}) = 5/1.5 = 3.33\); two-tailed \(p = 2(1-\Phi(3.33)) = 0.00087\).

(b) \(t = (47.5-50)/(4/\sqrt 9) = -2.5/1.333 = -1.875\), df = 8; =T.DIST.2T(1.875,8) = 0.098.

TestStatisticTwo-tailed pDecision
One-sample Z3.330.00087Reject \(H_0\)
One-sample t (df = 8)−1.8750.098Accept \(H_0\)

5. Result

(a) \(p = 0.00087 < 0.05\): the mean IQ differs significantly from 100. (b) \(p = 0.098 > 0.05\): the mean is not significantly different from 50. (c) The two-sample t-test decision follows from its returned p-value against 0.05.

Experiment 7 — Paired t-Test

1. Problem

The weights of 8 patients before (A2:A9) and after (B2:B9) a diet programme are recorded. Test whether the diet significantly reduced weight.

Before7278698085768274
After7076677883747972

2. Aim

To test the significance of the mean difference in a paired (before–after) design.

3. Formula

\[ t = \frac{\bar d}{s_d/\sqrt n}, \qquad df = n-1 \]

Applying it:

  1. Compute the differences \(d = \text{Before} - \text{After}\), their mean \(\bar d\) and SD \(s_d\).
  2. \(t = \bar d/(s_d/\sqrt n)\), df = \(n-1\).
  3. Quick test: =T.TEST(A2:A9,B2:B9,2,1) (2 = two-tailed, 1 = paired), or ToolPak t-Test: Paired Two Sample for Means.

4. Calculation

Blank working table:

StatisticValue
Mean (Before)
Mean (After)
Mean difference \(\bar d\)
\(s_d\)
t Stat (df = 7)
p (two-tail)
t Critical (two-tail)

Differences \(d\): 2, 2, 2, 2, 2, 2, 3, 2; \(\bar d = 2.125,\; s_d = 0.354\); \(t = 2.125/(0.354/\sqrt 8) = 17.0\).

StatisticValue
Mean (Before)77.000
Mean (After)74.875
Mean difference \(\bar d\)2.125
\(s_d\)0.354
t Stat (df = 7)17.0
p (two-tail)≈ 6 × 10⁻⁷
t Critical (two-tail)2.365

5. Result

\(t = 17.0 > t_{crit} = 2.365\) and \(p \approx 6\times 10^{-7} \ll 0.05\): the diet caused a statistically significant reduction in weight.

Experiment 8 — F-Test for Equality of Variances

1. Problem

Bolt diameters from two machines: Machine 1 (\(n_1 = 11\)) has sample variance 25; Machine 2 (\(n_2 = 16\)) has sample variance 16. Test \(H_0:\sigma_1^2 = \sigma_2^2\) at 5 %.

2. Aim

To test the equality of two population variances using the F-test.

3. Formula

\[ F = \frac{s_1^2}{s_2^2}, \qquad df = (n_1-1,\; n_2-1) \]

Applying it:

  1. Form \(F = s_1^2/s_2^2\) (larger variance on top), with df \((n_1-1, n_2-1)\).
  2. Critical value =F.INV.RT(0.025, 10, 15) (two-tailed at 5 %).
  3. Quick test on raw data: =F.TEST(A2:A12,B2:B17) returns the two-tailed p-value; or ToolPak F-Test Two-Sample for Variances.

4. Calculation

Blank working table:

QuantityValue
\(F = s_1^2/s_2^2\)
df
Critical F (upper 2.5 %)
Decision
QuantityValue
\(F = 25/16\)1.5625
df(10, 15)
Critical F =F.INV.RT(0.025,10,15)3.06
Decision1.5625 < 3.06 ⇒ Accept \(H_0\)

5. Result

Since \(F = 1.5625 < F_{crit} = 3.06\), \(H_0\) is accepted: the two machines have statistically equal variances.

Experiment 9 — Chi-Square Goodness of Fit

1. Problem

A die is rolled 60 times, giving observed frequencies for faces 1–6 of 8, 11, 9, 12, 10, 10. Test whether the die is fair at the 5 % level.

2. Aim

To test the goodness of fit of observed frequencies to the expected (uniform) frequencies using the chi-square test.

3. Formula

\[ \chi^2 = \sum \frac{(O-E)^2}{E}, \qquad df = k-1 = 5 \]

Applying it:

  1. Expected frequency for a fair die = 60/6 = 10 per face.
  2. Compute \((O-E)^2/E\) for each face and sum to get \(\chi^2\).
  3. Excel p-value: =CHISQ.TEST(observed, expected); critical value =CHISQ.INV.RT(0.05, 5).

4. Calculation

Blank working table:

FaceOE(O−E)²/E
1810
21110
3910
41210
51010
61010
Σ6060
FaceOE(O−E)²/E
18100.4
211100.1
39100.1
412100.4
510100
610100
Σ60601.0

\(\chi^2 = 1.0\); =CHISQ.TEST(...) → p ≈ 0.96; critical \(\chi^2_{0.05,5} = 11.07\).

5. Result

\(\chi^2 = 1.0 < 11.07\) (p ≈ 0.96 > 0.05), so \(H_0\) is accepted: the die is fair.

Experiment 10 — ANOVA (One-way and Two-way)

1. Problem

Yields for 3 fertilizers with 4 plots each are entered in columns A, B, C. Test whether the fertilizers differ (one-way ANOVA); outline the two-way ANOVA options.

F1F2F3
223020
263422
243224
283622

2. Aim

To test the equality of several group means using single-factor ANOVA and to identify the two-factor options in Excel.

3. Formula

\[ F = \frac{MS_{between}}{MS_{within}}, \qquad CD = t_{\alpha/2,\,df_E}\sqrt{\frac{2\,MS_E}{r}} \]

Applying it:

  1. Data → Data Analysis → ANOVA: Single Factor, input A1:C5 (with labels), α = 0.05.
  2. Read \(F\), p-value and \(F_{crit}\) from the output; if \(F > F_{crit}\), the means differ.
  3. For two-way analysis use ANOVA: Two-Factor Without Replication (one value per cell) or With Replication (multiple values per cell, adding an interaction row).

4. Calculation

Blank ANOVA table:

SourceSSdfMSFp-valueF crit
Between Groups
Within Groups———
Total————
SourceSSdfMSFp-valueF crit
Between Groups258.672129.3324.250.00024.26
Within Groups48.0095.33———
Total306.6711————

Critical difference: \(CD = t_{0.025,9}\sqrt{2(5.33)/4} = 2.262\times 1.633 = 3.69\).

5. Result

\(F = 24.25 > F_{crit} = 4.26\) (p ≈ 0.0002), so the fertilizers differ significantly. With \(CD = 3.69\), F2 (mean 33) differs from F1 (25) and F3 (22), while F1 and F3 do not differ.

Lab Record Format (to be followed for every experiment)

  1. 1. Problem — the dataset and what is to be computed or tested.
  2. 2. Aim — the statistic or test the experiment demonstrates.
  3. 3. Formula — the formula, then the numbered steps, with the Excel functions or ToolPak path, that apply it.
  4. 4. Calculation — the filled table with the values Excel returns.
  5. 5. Result — the decision (from p-value / critical value) and plain-language interpretation.
Note from Syllabus: MS-Excel practical problems must be done in the Computer Lab at least 4 hours per month.
← Previous: Unit 5 Next: Syllabus →