Topics Covered

On this page

1. 1. Recap of Hypothesis-Testing Framework
2. 2. Z-Test in Excel
3. 3. t-Test in Excel
4. 4. F-Test for Equality of Variances
5. 5. ANOVA in Excel
6. 6. Chi-Square Test
7. 7. Choosing the Right Excel Test
8. 8. Interpreting Excel Outputs — Best Practices
9. Real-world Case Studies
10. Key Take-aways

1. Recap of Hypothesis-Testing Framework

1. State \(H_0\) (null) and \(H_1\) (alternative).
2. Choose level of significance α (e.g., 0.05).
3. Compute the test statistic and its p-value.
4. Decision rule: reject \(H_0\) if p-value < α; else fail to reject.
5. Interpret in the problem context.

2. Z-Test in Excel

2.1 Function: Z.TEST

• One-tailed p-value: as returned.
• Two-tailed p-value: multiply by 2 (or compute 2 × MIN(p, 1 − p)).

2.2 Two-Sample Z-Test (Data Analysis ToolPak)

1. Data → Data Analysis → z-Test: Two Sample for Means → OK.
2. Variable 1 Range, Variable 2 Range (separate columns).
3. Hypothesised Mean Difference: usually 0.
4. Variance for Variable 1 / 2: enter known σ².
5. Output range; α = 0.05 default.

Output Contains

• Mean, Variance, Observations of each sample.
• Hypothesised Mean Difference.
• z statistic.
• P(Z ≤ z) one-tail, z Critical one-tail.
• P(Z ≤ z) two-tail, z Critical two-tail.

3. t-Test in Excel

3.1 Function: T.TEST

3.2 t-Test via Data Analysis ToolPak (richer output)

Data → Data Analysis → choose one of:

• t-Test: Paired Two Sample for Means
• t-Test: Two-Sample Assuming Equal Variances
• t-Test: Two-Sample Assuming Unequal Variances (Welch's)

Output Contains

• Mean, Variance, Observations of each sample.
• Pearson correlation (for paired test).
• Hypothesised Mean Difference.
• df.
• t statistic.
• P(T ≤ t) one-tail and t Critical one-tail.
• P(T ≤ t) two-tail and t Critical two-tail.

How to Choose the Right t-Test

Situation	Test (type)
Same subjects measured twice	Paired (type 1)
Two independent samples, σ's roughly equal	Equal variances (type 2)
Two independent samples, σ's clearly differ	Welch's (type 3)

4. F-Test for Equality of Variances

4.1 Function: F.TEST

4.2 F-Test via Data Analysis ToolPak

1. Data → Data Analysis → F-Test Two-Sample for Variances → OK.
2. Variable 1 Range, Variable 2 Range; α = 0.05 default.
3. Output gives F statistic, P(F ≤ f) one-tail, F Critical one-tail.

Tip: ToolPak places the larger variance in numerator if you arrange Variable 1 accordingly. The reported p-value is one-tailed; multiply by 2 for two-tailed.

5. ANOVA in Excel

5.1 ANOVA: Single Factor (One-way)

1. Arrange data so each group is one column (or one row).
2. Data → Data Analysis → ANOVA: Single Factor → OK.
3. Input Range (covers all groups together); Grouped By: Columns; tick Labels.
4. α = 0.05; output range.

Output Contains

• SUMMARY: Count, Sum, Average, Variance per group.
• ANOVA: Source / SS / df / MS / F / P-value / F-crit for Between Groups, Within Groups, Total.

5.2 ANOVA: Two-Factor Without Replication

Used when there is one observation per cell of a row × column design (e.g., RBD).

5.3 ANOVA: Two-Factor With Replication

Used when there are multiple observations per cell — analyses row, column, interaction and error.

Specify "Rows per sample" in the dialog — Excel needs equal rows per cell.

6. Chi-Square Test

6.1 Function: CHISQ.TEST

6.2 Steps for Independence Test

1. Lay out observed frequencies in a 2-D table.
2. Compute row totals, column totals, grand total.
3. Expected = (row total × column total) / grand total — fill a separate table.
4. Use =CHISQ.TEST(observed_range, expected_range) → p-value.
5. Also compute the \(\chi^2\) statistic for reporting: =SUMPRODUCT((O - E)^2 / E).

6.3 Goodness-of-Fit

Use observed counts and expected counts from your hypothesised distribution (Binomial, Poisson, Uniform, etc.). Same =CHISQ.TEST formula applies.

7. Choosing the Right Excel Test

Question	Excel Tool
Single mean, σ known, large n	=Z.TEST
Single mean, σ unknown, small n	Manual t (formula) or T.DIST.2T
Paired samples (before-after)	=T.TEST(...,2,1) or ToolPak Paired t
Two independent means, σ's equal	=T.TEST(...,2,2) or ToolPak Equal Var
Two independent means, σ's differ	=T.TEST(...,2,3) or ToolPak Welch
Equality of variances	=F.TEST or ToolPak F-Test
3+ group means	ToolPak ANOVA Single Factor
Two factors	ToolPak ANOVA Two-Factor
Independence / Goodness of Fit	=CHISQ.TEST

8. Interpreting Excel Outputs — Best Practices

1. Look at the p-value first. If p < α (often 0.05), reject H₀.
2. Compare the test statistic with the critical value as a cross-check.
3. Report direction (one-tailed) when the alternative is one-sided.
4. Always frame the conclusion in the original problem language: e.g., "There is significant evidence that the new training increases performance (paired t = 4.8, df = 19, p < 0.001)".
5. Mention effect size (mean difference, η², r²) — statistical significance ≠ practical significance.
6. Check assumptions: normality (use Q-Q plots / SKEW/KURT), equal variances (F-test before pooled t), independence (study design).

Real-world Case Studies

Business — A/B test on two web designs

Click-through rate of design A in column A, design B in column B (large samples). Use ToolPak's two-sample z-test on proportions (or chi-square on 2 × 2 table of clicks vs no-clicks). p-value decides which design is preferred.

Health Sciences — Drug effectiveness

Baseline and post-treatment blood pressure of 30 patients. Paired t-test in Excel decides whether the drug significantly reduces BP.

Social Sciences — Education method comparison

Three teaching methods, 20 students each. One-way ANOVA → if significant, follow up with pairwise t-tests with Bonferroni correction.

Key Take-aways

• Excel offers two paths: worksheet functions (live, formula-driven) and Data Analysis ToolPak (one-shot report).
• Z.TEST, T.TEST, F.TEST, CHISQ.TEST return p-values directly.
• ToolPak gives richer output: test statistic, df, critical values, both one- and two-tailed p-values.
• ANOVA Single Factor and Two-Factor handle multiple-group comparisons.
• Always interpret results in plain language and check assumptions.