Statistics with Excel

AAEC 2301 · Agricultural Statistics · Texas Tech University

Data Analysis ToolPak Statistical Functions Visualization Hypothesis Testing Regression

Before you start: Enable the Data Analysis ToolPak in Excel. Go to File → Options → Add-ins → Manage: Excel Add-ins → Go, check Analysis ToolPak, and click OK. This unlocks tools for regression, t-tests, ANOVA, histograms, and more. All examples use agricultural and economic datasets to match this course.

Chapter 1: Data Collection

What This Chapter Is About

Good statistics starts with good data. Before any analysis, you need to know where your data came from, how it was collected, and whether it is representative of the population you care about. In agricultural economics, this means understanding farm surveys, USDA datasets, and sampling designs for crop yield studies.

The Process of Statistics

Every statistical study follows four steps:

1. Identify the research objective — What question are you answering? (Example: “What is the mean cotton yield on Lubbock County farms this season?”)
2. Collect data — Design a study and gather observations from a sample of the population.
3. Organize and summarize — Build frequency tables, compute averages, and draw graphs (Chapters 2–3).
4. Draw conclusions — Use inferential methods to generalize from the sample to the population (Chapters 8–11).

A population is the complete set of all individuals of interest. A sample is the subset actually observed. Because studying an entire population is usually impractical, we study a sample and use statistics to make inferences about the population.

Types of Variables

Before collecting data, identify the type of variable — this determines which formulas and graphs apply.

Variable	Type	Discrete or Continuous
Crop type (Cotton, Corn, Sorghum)	Qualitative	— (no arithmetic)
Number of farms in a county	Quantitative	Discrete (counted)
Cotton yield (lbs/acre)	Quantitative	Continuous (measured)
Farm size (Small / Medium / Large)	Qualitative	— (no arithmetic)
Daily temperature (°F)	Quantitative	Continuous (measured)

Rule of thumb: if you count to get the value → discrete; if you measure → continuous.

Sampling Methods

Method	How it works	When to use
Simple random	Every individual has an equal chance of selection	General surveys with a complete list (frame)
Stratified	Divide population into groups (strata); sample randomly within each	When subgroups (farm sizes, crop types) differ from one another
Systematic	Select every k-th individual; k = N/n	When a frame exists and ordering is random
Cluster	Randomly select groups (clusters); survey all members	When travel cost is high and clusters resemble the full population

Key Excel Skills

Task	Excel Tool / Function
Enter and label a dataset	Manual entry with row/column headers
Generate random numbers	=RAND() and =RANDBETWEEN(low, high)
Select a simple random sample	RAND() + RANK() to assign random order
Sort data	Data → Sort
Filter rows by condition	Data → Filter

Step-by-Step: Simple Random Sampling

Suppose you have a list of 200 farms in Lubbock County and you want to randomly select 30 for a survey.

1. In column A, enter farm IDs 1 through 200.
2. In column B, type =RAND() in cell B2 and drag it down to B201. This assigns each farm a random number.
3. In column C, use =RANK(B2, $B$2:$B$201, 1) to rank farms by their random number.
4. Sort the entire table by column C ascending.
5. The first 30 rows after sorting are your simple random sample.

📗 FarmSurvey.xlsx — Excel

B2 fx =RAND()

	A	B	C
1	Farm ID	Random #	Sample Rank
2	Farm_001	0.2341	47
3	Farm_002	0.8712	175
4	Farm_003	0.0523	11
5	Farm_004	0.5634	112
6	Farm_005	0.1187	24
7	Farm_006	0.9341	187
8	Farm_007	0.0441	9
9	⋮	⋮	⋮

Cell B2 is selected — formula bar shows =RAND(). Green-shaded ranks ≤ 30 become your sample. Press F9 to regenerate; paste column B as Values before sorting to freeze the sample.

Types of Sampling Designs

• Simple random sampling: Every unit has an equal chance — use RAND() as above.
• Stratified sampling: Divide farms by size (small, medium, large), then sample randomly within each stratum using RANDBETWEEN.
• Systematic sampling: Select every kth farm from a sorted list. Compute k = N/n (e.g., every 7th farm if N=200, n=30).

Common Mistake

Using RAND() without freezing the value means your sample changes every time Excel recalculates. Always paste as Values Only before sorting.

---

Chapter 2: Summarizing Data in Tables and Graphs

What This Chapter Is About

Raw numbers are hard to interpret. Frequency distributions, bar charts, and histograms compress hundreds of observations into patterns you can communicate and act on.

Frequency Distributions

A frequency distribution table organizes data into categories (qualitative) or equal-width intervals (quantitative) and counts how many observations fall in each group.

Relative frequency converts raw counts into proportions so you can compare distributions of different sizes:

\[\text{Relative frequency} = \frac{\text{frequency (count in class)}}{\sum \text{all frequencies}} = \frac{f}{\sum f}\]

Example: If 28 of 80 farms grow Cotton, the relative frequency = 28/80 = 0.35 (35%).

The sum of all relative frequencies must equal exactly 1 (or 100%).

Terms for grouped quantitative data:

Term	Definition
Class	An interval of values (e.g., 800–900 lbs/acre)
Lower class limit	Smallest value belonging to the class
Upper class limit	Largest value belonging to the class
Class width	Lower limit of next class − lower limit of this class
Class midpoint	(Lower limit + Upper limit) / 2 — represents the class in calculations
Cumulative frequency	Running total of frequencies up through a class

Types of Graphs

Graph	Data type	Key rule
Bar chart	Qualitative (categories)	Bars do not touch; order doesn’t matter
Histogram	Quantitative (grouped)	Bars do touch (no gaps); intervals are equal
Frequency polygon	Quantitative (grouped)	Connect midpoints with line segments; close to x-axis at both ends
Stem-and-leaf	Quantitative (raw)	Preserves original data values; quick to construct by hand

Key Excel Skills

Task	Excel Tool / Function
Count occurrences of a category	=COUNTIF(range, criteria)
Count with two conditions	=COUNTIFS(r1, c1, r2, c2)
Relative frequency	=count / total
Bar chart / pie chart	Insert → Charts
Histogram	Data → Data Analysis → Histogram
Cross-tabulation	Insert → PivotTable

Step-by-Step: Frequency Table for Crop Types

Suppose column A has the crop type (Corn, Cotton, Sorghum, Wheat) for 80 farms.

1. List the four crop names in cells D2:D5.
2. In E2, enter =COUNTIF($A$2:$A$81, D2). Copy down to E5.
3. In F2, enter =E2/SUM($E$2:$E$5). Copy down to F5. Format as Percentage.
4. Select D2:E5 and insert a Clustered Bar Chart (Insert → Bar Chart).
5. Right-click bars → Add Data Labels to show counts.

📗 CropSurvey.xlsx — Excel

E2 fx =COUNTIF($A$2:$A$81,D2)

	A	B	C	D	E	F
1	Crop Type	Farm Size	Yield	Category	Count	Rel. Freq.
2	Cotton	Large	842	Corn	22	27.5%
3	Corn	Small	61	Cotton	28	35.0%
4	Corn	Large	74	Sorghum	18	22.5%
5	Sorghum	Medium	45	Wheat	12	15.0%
6	Cotton	Small	789	Total	80	100.0%
7	⋮	⋮	⋮

E2 is selected — formula bar shows =COUNTIF($A$2:$A$81,D2). Drag E2 down to E5 to count all four crops. Column F divides each count by 80 for relative frequencies.

Step-by-Step: Histogram for Continuous Data

1. Enter corn yields (bu/acre) in column A. Enter bin edges (80, 100, 120, 140, 160, 180) in column B.
2. Go to Data → Data Analysis → Histogram.
3. Set Input Range to column A and Bin Range to column B. Check Chart Output. Click OK.
4. Right-click the histogram bars → Format Data Series → set Gap Width to 0 (histograms have no gaps between bars).

Shapes to Recognize

• Symmetric / Bell-shaped: values cluster around the center.
• Right-skewed: long tail to the right — common in farm income data.
• Left-skewed: long tail to the left.
• Bimodal: two peaks — may indicate two sub-populations (irrigated vs. dryland).

---

Chapter 3: Numerically Summarizing Data

What This Chapter Is About

A single number can summarize a whole distribution. This chapter covers measures of center (mean, median, mode) and spread (standard deviation, variance, IQR). Excel’s Descriptive Statistics tool generates all of them at once.

Measures of Central Tendency

(1) Mean — the arithmetic average; the balancing point of the data.

\[\mu = \frac{\sum X_i}{N} \quad \text{(population mean, } N \text{ = population size)}\]

\[\bar{x} = \frac{\sum x_i}{n} \quad \text{(sample mean, } n \text{ = sample size)}\]

A key property: the sum of all deviations from the mean is always exactly zero — \(\sum(x_i - \bar{x}) = 0\).

(2) Median — the middle value when data are sorted from smallest to largest.

• If n is odd: median = the value at position \(\dfrac{n+1}{2}\).
• If n is even: median = average of values at positions \(\dfrac{n}{2}\) and \(\dfrac{n}{2}+1\).

The median is resistant to extreme values (outliers). Use it for skewed distributions such as income or housing prices.

(3) Mode — the value(s) that appear most often. A dataset can be unimodal, bimodal, or have no mode.

Measures of Dispersion

(1) Range = Largest value − Smallest value. Simple to compute but sensitive to outliers.

(2) Variance — average squared deviation from the mean. We square the deviations so that positive and negative errors do not cancel out:

\[\sigma^2 = \frac{\sum(X_i - \mu)^2}{N} \quad \text{(population variance)}\]

\[s^2 = \frac{\sum(x_i - \bar{x})^2}{n-1} \quad \text{(sample variance)}\]

Why n − 1 for samples? Dividing by n − 1 (Bessel’s correction) adjusts for the fact that a sample systematically underestimates the true population spread when you use \(\bar{x}\) instead of the unknown \(\mu\).

A computationally handy equivalent for the numerator is the sum of squares (SS):

\[SS = \sum x_i^2 - \frac{(\sum x_i)^2}{n} \quad \Rightarrow \quad s^2 = \frac{SS}{n-1}\]

(3) Standard deviation — square root of variance, in the same units as the original variable:

\[\sigma = \sqrt{\sigma^2} \qquad s = \sqrt{s^2}\]

The Empirical Rule

For a bell-shaped (symmetric) distribution, approximately:

Interval	% of observations
\(\mu \pm 1\sigma\)	~68%
\(\mu \pm 2\sigma\)	~95%
\(\mu \pm 3\sigma\)	~99.7%

z-Scores

A z-score (standardized value) tells you how many standard deviations an observation is above or below the mean:

\[z = \frac{x - \mu}{\sigma} \quad \text{(population)} \qquad z = \frac{x - \bar{x}}{s} \quad \text{(sample)}\]

z > 0 means the value is above the mean; z < 0 means below. Values with |z| > 3 are often considered outliers.

Key Excel Skills

Statistic	Excel Function
Mean	=AVERAGE(range)
Median	=MEDIAN(range)
Mode	=MODE.SNGL(range)
Standard deviation (sample)	=STDEV.S(range)
Variance (sample)	=VAR.S(range)
Range	=MAX(range) - MIN(range)
Interquartile range	=QUARTILE.INC(range,3) - QUARTILE.INC(range,1)
Percentile	=PERCENTILE.INC(range, k) where k is 0–1
All at once	Data → Data Analysis → Descriptive Statistics

Step-by-Step: Descriptive Statistics Tool

1. Enter cotton yield data (lbs/acre) in column A, rows 1–51 (row 1 = header “Yield”).
2. Go to Data → Data Analysis → Descriptive Statistics.
3. Set Input Range to $A$1:$A$51. Check Labels in first row.
4. Choose a new worksheet for output. Check Summary Statistics and Confidence Level for Mean (95%). Click OK.

📗 CottonYield_DescStats.xlsx — Excel

B3 fx 847.24

	A	B
1	Yield (lbs/acre)
2
3	Mean	847.24
4	Standard Error	12.43
5	Median	851.00
6	Mode	#N/A
7	Standard Deviation	87.91
8	Sample Variance	7728.2
9	Kurtosis	-0.312
10	Skewness	0.182
11	Range	412.0
12	Minimum	623.0
13	Maximum	1035.0
14	Sum	42362.0
15	Count	50
16
17	Confidence Level (95.0%)	24.89

Output from Data → Data Analysis → Descriptive Statistics (green = generated by Excel). Mean = 847.24 lbs/acre. Confidence Level row (17) gives the margin of error for a 95% CI: 847.24 ± 24.89.

Worked Example: Manual Calculation of Mean, Variance, and SD

The ToolPak gives you results in one click, but tracing through the manual steps builds real intuition for what these statistics measure. The spreadsheet below is exactly what you would build in Excel: a data column, a mean column, a deviation column (X − x̄), and a squared deviation column — followed by step-by-step manual calculations on the left and equivalent Excel shortcuts on the right.

Agricultural Example — Cotton yield (lbs/acre) for 9 farms in Lubbock County:

📗 CottonYield_Manual.xlsx — Excel

D9 fx =AVERAGE($C$9:$C$17)

	B	C	D	E	F	G	H	I
8	Farm Name	X=Yield	Mean	(X−Mean)	(X−Mean)²
9	Pearson Farm	805	828.0	−23.0	529.00
10	Miller Farm	780	828.0	−48.0	2,304.00
11	Garcia Farm	780	828.0	−48.0	2,304.00
12	Johnson Farm	850	828.0	22.0	484.00
13	Davis Farm	840	828.0	12.0	144.00
14	Wilson Farm	860	828.0	32.0	1,024.00
15	Martinez Farm	860	828.0	32.0	1,024.00
16	Taylor Farm	810	828.0	−18.0	324.00
17	Anderson Farm	867	828.0	39.0	1,521.00
18
19	Manual Calculations:					Excel Statistical Functions:
20	ΣX	7,452	=SUM(C9:C17)
21	No of obs	9	=COUNT(C9:C17)
22	Mean	828.0	=C20/C21			Mean	828.0	=AVERAGE(C9:C17)
23
24	(N+1)/2	5	=(C21+1)/2
25	Median a/	840	=SMALL(C9:C17,C24)			Median b/	840.0	=MEDIAN(C9:C17)
26
27	Because manual mode calculation involves multiple steps and is time consuming, use the Excel MODE function.
28	Interpret MODE as the "most frequently occurring yield value."					Mode c/	780.0	=MODE(C9:C17)
29
30	Max	867	=MAX(C9:C17)
31	Min	780	=MIN(C9:C17)			A range function is not available in Excel — calculate manually.
32	Range	87	=C30-C31
33
34	Σ(X−Mean)	0.0	=SUM(E9:E17)
35	Σ(X−Mean)²	9,658.0	=SUM(F9:F17)
36	No of obs	9	=COUNT(F9:F17)
37	Variance_Pop	1,073.11	=C35/C36			VARP	1,073.11	=VARP(C9:C17)
38	Std Dev_Pop	32.76	=C37^0.5			STDEVP	32.76	=STDEVP(C9:C17)
39	Variance_Sample	1,207.25	=C35/(C36-1)			VAR	1,207.25	=VAR(C9:C17)
40	Std Dev_Sample	34.75	=C39^0.5			STDEV	34.75	=STDEV(C9:C17)

Column D uses =AVERAGE($C$9:$C$17) with absolute references — drag down and the mean stays fixed. Column E computes =C9-D9 (deviation from mean); Column F computes =E9^2 (squared deviation). Manual steps on the left and Excel functions on the right produce identical answers. Use Excel functions for speed; trace through the manual steps once to understand where the numbers come from.

Key takeaways from this example:

• Σ(X − x̄) = 0 always — positive and negative deviations cancel out exactly. This is why we must square the deviations before summing.
• Sample vs. population: divide by (n − 1) for sample data (surveys, experiments). In Excel use STDEV.S and VAR.S, or the older STDEV and VAR.
• Mode = 780: Miller Farm and Garcia Farm both reported 780 lbs/acre (Wilson and Martinez tied at 860, but Excel MODE returns the first repeated value encountered in the range).
• The Descriptive Statistics ToolPak above produces all of these at once — but this step-by-step layout shows exactly where each number comes from.

Five-Number Summary and Box Plots

1. Compute MIN, Q1, Median, Q3, MAX using QUARTILE.INC(data, 0/1/2/3/4).
2. In Excel 2016+: select your data → Insert → Insert Statistic Chart → Box and Whisker.

Interpreting Standard Deviation

SD = 87.91 lbs/acre means most farms fall within ±88 lbs/acre of the mean. By the Empirical Rule: ~68% of values lie within 1 SD, ~95% within 2 SD, and ~99.7% within 3 SD of the mean (for bell-shaped distributions).

---

Chapter 4: Describing the Relation between Two Variables

What This Chapter Is About

Does more irrigation water mean higher yields? Does fertilizer price affect application rate? Scatter plots, correlation, and simple linear regression answer these questions.

Scatter Diagrams

A scatter diagram plots each observation as a point (x, y). By convention: - The independent (explanatory) variable x goes on the horizontal axis. - The dependent (response) variable y goes on the vertical axis. - Do not connect the points — each point is a separate observation.

The pattern reveals the direction (positive / negative / none) and form (linear / curved) of the association.

Linear Correlation Coefficient

The linear correlation coefficient r measures the strength and direction of the linear association between two quantitative variables:

\[r = \frac{\sum xy - \dfrac{(\sum x)(\sum y)}{n}}{\sqrt{\left(\sum x^2 - \dfrac{(\sum x)^2}{n}\right)\left(\sum y^2 - \dfrac{(\sum y)^2}{n}\right)}}\]

Properties of r:

Value of r	Interpretation
r = +1	Perfect positive linear association
r close to +1	Strong positive linear association
r ≈ 0	Little or no linear association
r close to −1	Strong negative linear association
r = −1	Perfect negative linear association

Important: r is unitless — it does not change if you rescale x or y. Correlation measures only linear association; a strong curved relationship can give r ≈ 0.

Least-Squares Regression

The least-squares regression line \(\hat{y} = b_0 + b_1 x\) minimizes the sum of the squared vertical distances (errors) between the observed values y and the predicted values ŷ.

Formulas for the coefficients:

\[b_1 = \frac{\sum xy - n\bar{x}\bar{y}}{\sum x^2 - n(\bar{x})^2} \quad \text{(slope)}\]

\[b_0 = \bar{y} - b_1\bar{x} \quad \text{(intercept)}\]

Interpretation: - b₁ (slope): for each 1-unit increase in x, the predicted y changes by b₁ units (on average). - b₀ (intercept): predicted y when x = 0. This is meaningful only if x = 0 is within (or close to) the observed range of the data.

Coefficient of Determination

\[R^2 = r^2 = \frac{\text{Explained variation in } y}{\text{Total variation in } y}\]

R² is the proportion of the total variation in y that is accounted for by the linear relationship with x. For example, R² = 0.87 means 87% of the variation in yield is explained by irrigation water; the remaining 13% is due to other factors (soil quality, weather, etc.).

Scope of the model: use the regression line to predict y only for x-values within (or very close to) the range of observed x. Extrapolating far beyond the data is unreliable because the linear relationship may not continue.

Key Excel Skills

Task	Excel Tool / Function
Scatter plot	Insert → Scatter Chart
Correlation coefficient	=CORREL(x_range, y_range)
Slope	=SLOPE(y_range, x_range)
Intercept	=INTERCEPT(y_range, x_range)
R-squared	=RSQ(y_range, x_range)
Full regression output	Data → Data Analysis → Regression

Step-by-Step: Scatter Plot and Trendline

1. Column A: water applied (acre-inches); Column B: corn yield (bu/acre) for 45 farms.
2. Select both columns → Insert → Scatter → Scatter with Only Markers.
3. Right-click a point → Add Trendline → select Linear → check Display Equation and Display R-squared.
4. In a cell: =CORREL(A2:A46, B2:B46) for the correlation coefficient.

Step-by-Step: Regression with the ToolPak

1. Data → Data Analysis → Regression.
2. Y Range = yield column; X Range = water column. Check Labels and Residuals. Click OK.

📗 IrrigationRegression.xlsx — Excel

A1 fx SUMMARY OUTPUT

	A	B	C	D	E	F
1	SUMMARY OUTPUT
2
3	Regression Statistics
4	Multiple R	0.7821
5	R Square	0.6117
6	Adjusted R Square	0.6028
7	Standard Error	8.41
8	Observations	45
9
10	ANOVA	df	SS	MS	F	Significance F
11	Regression	1	4823.1	4823.1	68.23	0.0000
12	Residual	43	3040.2	70.7
13	Total	44	7863.3
14
15		Coefficients	Std Error	t Stat	P-value	Lower 95%
16	Intercept	12.40	4.21	2.95	0.005	3.91
17	Water Applied (ac-in)	1.87	0.23	8.26	0.000	1.42

Regression output from the Data Analysis ToolPak. R² = 0.61 means 61% of yield variation is explained by water applied. The slope 1.87 means each additional acre-inch of water increases yield by 1.87 bu/acre (p < 0.001, highly significant).

Caution

Correlation measures linear association only. Always inspect the scatter plot first — a low correlation does not rule out a nonlinear relationship.

---

Chapter 5: Probability

What This Chapter Is About

Probability underpins all of statistics. Excel lets you compute empirical probabilities directly from data using COUNTIF and build contingency tables with PivotTables.

Key Excel Skills

Task	Excel Tool / Function
Count outcomes meeting a condition	=COUNTIF(range, criteria)
Count with two conditions	=COUNTIFS(r1, c1, r2, c2)
Compute a proportion	= count / COUNTA(range)
Complement rule	= 1 - P(A)
Contingency table	Insert → PivotTable

Step-by-Step: Empirical Probability from Data

Suppose you have 300 farm records. Column A = Irrigation (Yes/No); Column B = Above-Average Yield (Yes/No).

1. =COUNTIF(A2:A301, "Yes") → farms using irrigation.
2. =COUNTIFS(A2:A301,"Yes", B2:B301,"Yes") → irrigated farms with high yield.
3. Divide each count by 300 for probabilities.
4. Conditional probability: P(High Yield | Irrigation) = P(Both) / P(Irrigation).

Constructing a Contingency Table

Insert → PivotTable with Irrigation as rows, Yield Category as columns, and Count of farms as values. Row and column totals give marginal probabilities.

Checking Independence

Two events A and B are independent if P(A and B) = P(A) × P(B). Compute both sides in Excel and compare.

---

Chapter 6: Discrete Probability Distributions

What This Chapter Is About

Some outcomes are counts: diseased plants per field, farms applying for a subsidy, defects per harvest batch. The binomial and Poisson distributions model these. Excel has built-in functions for both.

Key Excel Skills

Task	Excel Function
Binomial P(X = k)	=BINOM.DIST(k, n, p, FALSE)
Binomial P(X ≤ k)	=BINOM.DIST(k, n, p, TRUE)
Poisson P(X = k)	=POISSON.DIST(k, lambda, FALSE)
Poisson P(X ≤ k)	=POISSON.DIST(k, lambda, TRUE)
Expected value (discrete)	=SUMPRODUCT(values, probabilities)

Step-by-Step: Binomial Distribution

An extension survey finds 40% of small farms adopt a new pest-control method. You sample 10 farms. P(exactly 4 adopt)?

1. =BINOM.DIST(4, 10, 0.4, FALSE) → 0.2508
2. P(at most 4): =BINOM.DIST(4, 10, 0.4, TRUE) → 0.6331
3. P(more than 4): =1 - BINOM.DIST(4, 10, 0.4, TRUE) → 0.3669

Building a Probability Distribution Table

1. Column A: k values 0 through 10.
2. Column B: =BINOM.DIST(A2, 10, 0.4, FALSE) dragged down.
3. Insert a Bar Chart of columns A and B to visualize the distribution.
4. Check: =SUMPRODUCT(A2:A12, B2:B12) should equal n × p = 4.

Poisson Distribution

A field averages 3 pest infestations per season. P(X = 5)?

• =POISSON.DIST(5, 3, FALSE) → 0.1008
• P(X ≤ 5): =POISSON.DIST(5, 3, TRUE) → 0.9161

---

Chapter 7: The Normal Probability Distribution

What This Chapter Is About

The normal (bell-shaped) distribution is the cornerstone of statistical inference. Crop yields, soil nutrient levels, and many economic variables follow approximately normal distributions.

Key Excel Skills

Task	Excel Function
P(X ≤ x) for normal distribution	=NORM.DIST(x, mean, sd, TRUE)
Find x given P(X ≤ x) = p	=NORM.INV(p, mean, sd)
Standardize: z = (x − μ) / σ	=STANDARDIZE(x, mean, sd)
P(Z ≤ z) standard normal	=NORM.S.DIST(z, TRUE)
Find z given probability	=NORM.S.INV(p)

Step-by-Step: Computing Normal Probabilities

Wheat yields: μ = 45 bu/acre, σ = 8 bu/acre.

• P(X < 50): =NORM.DIST(50, 45, 8, TRUE) → 0.7340
• P(X > 55): = 1 - NORM.DIST(55, 45, 8, TRUE) → 0.1056
• P(40 < X < 55): =NORM.DIST(55,45,8,TRUE) - NORM.DIST(40,45,8,TRUE) → 0.6540

Finding a Percentile

What yield marks the 90th percentile?

=NORM.INV(0.90, 45, 8) → 55.25 bu/acre

Drawing the Bell Curve in Excel

1. Column A: x values from 13 to 77 in steps of 1 (=SEQUENCE(65, 1, 13, 1) in Excel 365, or type manually).
2. Column B: =NORM.DIST(A2, 45, 8, FALSE) dragged down.
3. Insert a Line Chart using columns A and B. This draws the bell curve for your distribution.

Checking Normality

From the Descriptive Statistics output (Chapter 3): Skewness near 0 and Excess Kurtosis near 0 suggest normality. A skewness above 1 or below −1 indicates significant departure from normality.

---

Chapter 8: Sampling Distributions

What This Chapter Is About

A statistic like the sample mean x̄ is itself random — it changes with each new sample. Its sampling distribution describes that variability and is what makes confidence intervals and hypothesis tests valid.

Key Concepts

The Central Limit Theorem (CLT): For large enough n, the distribution of x̄ is approximately normal with mean μ and standard error σ/√n — regardless of the population’s shape.

Step-by-Step: Simulating the CLT in Excel

1. In Sheet1, column A (rows 1–1000): enter =RANDBETWEEN(1, 100) for a uniform “population.” Freeze values (Copy → Paste Special → Values).
2. In Sheet2, create a 200-row × 30-column table. Each cell: =INDEX(Sheet1!$A$1:$A$1000, RANDBETWEEN(1,1000)) — this draws 30 random values per row (200 samples of n = 30).
3. In column AE of Sheet2: =AVERAGE(A2:AD2) for each row — these are 200 sample means.
4. Build a Histogram of those 200 means. Even though the population was uniform (flat), the histogram of means looks approximately bell-shaped — that’s the CLT.

Standard Error

• SE = σ/√n (if population SD is known)
• SE ≈ s/√n (estimated from sample)
• From Descriptive Statistics output: the Standard Error row gives s/√n directly.

Larger n → smaller SE → narrower sampling distribution → more precise estimates.

---

Chapter 9: Estimating the Value of a Parameter

What This Chapter Is About

We rarely know the true population mean μ. A confidence interval uses sample data to estimate a plausible range for μ with a stated level of confidence (90%, 95%, or 99%).

Key Excel Skills

Task	Excel Function / Tool
Margin of error (σ unknown, use t)	=CONFIDENCE.T(alpha, stdev_s, n)
Margin of error (σ known, use z)	=CONFIDENCE.NORM(alpha, sigma, n)
t critical value (two-tailed)	=T.INV.2T(alpha, df)
z critical value	=NORM.S.INV(1 - alpha/2)
Full CI from ToolPak	Descriptive Statistics → check Confidence Level

Step-by-Step: 95% CI for Mean Yield

Sample: n = 36 farms, x̄ = 48 bu/acre, s = 10 bu/acre.

1. SE = = 10 / SQRT(36) → 1.667
2. t* (df = 35, two-tailed) = =T.INV.2T(0.05, 35) → 2.030
3. Margin of error = = 2.030 * 1.667 → 3.38
4. CI: (44.62, 51.38) bu/acre

Or use the shortcut: =CONFIDENCE.T(0.05, 10, 36) returns the margin directly.

📗 ConfidenceInterval.xlsx — Excel

B8 fx =CONFIDENCE.T(0.05,B3,B2)

	A	B
1	95% CI for Mean Corn Yield
2	Sample size (n)	36
3	Sample St. Dev. (s)	10.00
4	Sample Mean (x̄)	48.00
5
6	Standard Error (s/√n)	1.667
7	t* critical value (df=35)	2.030
8	Margin of Error	3.38
9
10	Lower Bound (x̄ − ME)	44.62
11	Upper Bound (x̄ + ME)	51.38
12
13	95% CI: (44.62, 51.38) bu/acre

B8 (selected) uses =CONFIDENCE.T(0.05, B3, B2) — alpha=0.05, s=10, n=36. Lower and upper bounds in rows 10–11 give the full 95% confidence interval.

Confidence Interval for a Proportion

42 of 100 farms adopted cover cropping. p̂ = 0.42.

• SE = =SQRT(0.42 * 0.58 / 100) → 0.0494
• z* = =NORM.S.INV(0.975) → 1.960
• ME = = 1.96 * 0.0494 → 0.097
• CI: (0.323, 0.517) — between 32.3% and 51.7% of farms adopt cover cropping.

---

Chapter 10: Hypothesis Tests Regarding a Parameter

What This Chapter Is About

Hypothesis testing is the formal framework for deciding whether data gives enough evidence to reject a claim about a population. Is average farm income above $50,000? Has a new fertilizer pushed average yield beyond 45 bu/acre?

Key Excel Skills

Test	Excel Tool
t-statistic (manual)	= (xbar - mu0) / (s / SQRT(n))
p-value (two-tailed)	=T.DIST.2T(ABS(t_stat), df)
p-value (right-tailed)	=T.DIST.RT(t_stat, df)
p-value (left-tailed)	=T.DIST(t_stat, df, TRUE)
t critical value	=T.INV.2T(alpha, df)

Step-by-Step: One-Sample t-Test

Claim: Mean corn yield in the region exceeds 42 bu/acre.

H₀: μ = 42    H₁: μ > 42   (right-tailed)

Sample: n = 25, x̄ = 45.2, s = 9.5.

📗 HypothesisTest_Ch10.xlsx — Excel

B9 fx =(B3-B4)/(B5/SQRT(B2))

	A	B
1	One-Sample t-Test (Right-Tailed)
2	Sample size (n)	25
3	Sample Mean (x̄)	45.20
4	Hypothesized Mean (μ₀)	42.00
5	Sample Std Dev (s)	9.50
6
7	Standard Error (s/√n)	1.900
8	Degrees of Freedom	24
9	t statistic	1.684
10	p-value (right-tailed)	0.0526
11	α level	0.05
12
13	Decision (α = 0.05)	Fail to Reject H₀
14	Decision (α = 0.10)	Reject H₀

B9 is selected — formula =(B3-B4)/(B5/SQRT(B2)) computes the t-statistic. B10 uses =T.DIST.RT(B9,B8) for the one-tailed p-value. At α = 0.05 the p-value (0.053) just exceeds the cutoff, so we fail to reject; at α = 0.10 we reject.

Decision Rules

Comparison	Decision
p-value < α	Reject H₀
p-value ≥ α	Fail to reject H₀

Testing a Proportion

More than 30% of farms adopted GPS-guided equipment? Sample: n = 120, p̂ = 0.38.

• z = = (0.38 - 0.30) / SQRT(0.30*0.70/120) → 1.913
• p-value (right-tailed): = 1 - NORM.S.DIST(1.913, TRUE) → 0.028
• At α = 0.05: Reject H₀. Evidence supports > 30% adoption.

---

Chapter 11: Inference on Two Population Parameters

What This Chapter Is About

You often want to compare two groups — irrigated vs. non-irrigated farms, organic vs. conventional yields, income before and after a policy change.

Key Excel Skills

Test	Excel Tool
F-test for equal variances	Data Analysis → F-Test Two-Sample for Variances
Two-sample t-test (equal var.)	Data Analysis → t-Test: Two-Sample Assuming Equal Variances
Two-sample t-test (unequal var.)	Data Analysis → t-Test: Two-Sample Assuming Unequal Variances
Paired t-test	Data Analysis → t-Test: Paired Two Sample for Means

Step-by-Step: Independent Two-Sample t-Test

Question: Is mean cotton yield different between drip-irrigated and furrow-irrigated farms?

1. Column A: yields for 20 drip-irrigated farms. Column B: 20 furrow-irrigated.
2. Run the F-test first to decide which t-test to use.
3. Data → Data Analysis → t-Test: Two-Sample Assuming Equal Variances (or Unequal, based on F-test).
4. Variable 1 Range = column A; Variable 2 Range = column B. Hypothesized Mean Difference = 0. Alpha = 0.05.

📗 TwoSample_tTest.xlsx — Excel

A1 fx t-Test: Two-Sample Assuming Equal Variances

	A	B	C
1	t-Test: Two-Sample Assuming Equal Variances
2		Drip	Furrow
3	Mean	892.4	851.7
4	Variance	7854.2	8102.6
5	Observations	20	20
6	Pooled Variance	7978.4
7	Hypothesized Mean Difference	0
8	df	38
9	t Stat	1.612
10	P(T<=t) one-tail	0.057
11	t Critical one-tail	1.686
12	P(T<=t) two-tail	0.115
13	t Critical two-tail	2.024

Two-sample t-test output from the ToolPak. Two-tailed p-value = 0.115 > 0.05 → fail to reject H₀. No statistically significant difference in mean yield between drip and furrow irrigation at the 5% level.

Paired t-Test

Same farms measured before and after a soil amendment:

Data → Data Analysis → t-Test: Paired Two Sample for Means. Variable 1 = before yields; Variable 2 = after yields. This accounts for farm-level variation and is more powerful than the two-sample t-test when data is paired.

When to Use Which Test

Situation	Test
Two separate groups, similar variances	Two-sample t (equal var.)
Two separate groups, different variances	Welch’s t (unequal var.)
Same subjects measured twice	Paired t-test

---

Chapter 12: Additional Inferential Methods

What This Chapter Is About

This chapter covers the chi-square test for categorical data and extends linear regression to formal inference — testing whether predictors are significant and building prediction intervals.

Part A: Chi-Square Tests

Task	Excel Function
Chi-square p-value (goodness-of-fit or independence)	=CHISQ.TEST(observed_range, expected_range)
Chi-square critical value	=CHISQ.INV.RT(alpha, df)
Chi-square statistic (manual)	=SUMPRODUCT((O-E)^2/E)

Step-by-Step: Test of Independence

Question: Is crop type independent of farm size?

1. Build a 3×2 observed table from your data using COUNTIFS.
2. Compute expected frequencies: E = (Row Total × Column Total) / Grand Total for each cell.
3. =CHISQ.TEST(observed_range, expected_range) → returns the p-value directly.
4. df = (rows − 1)(columns − 1). Compare p to α = 0.05.

Part B: Regression Inference

After running the ToolPak regression (see Chapter 4), the output table provides all inference:

Column	What to Read
Coefficients	Estimated slope(s) and intercept
P-value	Is this variable significant? (< 0.05 = yes)
Lower/Upper 95%	Confidence interval for the coefficient
Significance F	Overall model p-value

Prediction: ŷ = b₀ + b₁ × x_new. Enter your new x value into =Intercept + Slope * x_new.

---

Appendix B: One-Way ANOVA

What This Chapter Is About

When comparing means across three or more groups — four fertilizer rates, three irrigation methods — use ANOVA instead of running multiple t-tests (which inflates Type I error).

Step-by-Step: One-Way ANOVA

Question: Does mean corn yield differ across four fertilizer rates (Low, Medium, High, Very High)?

1. Organize data: each column = one group. Each row = one farm within that group.
2. Data → Data Analysis → ANOVA: Single Factor.
3. Input Range = all four columns. Check Labels in First Row. Alpha = 0.05. Click OK.

📗 ANOVA_FertilizerYield.xlsx — Excel

A1 fx Anova: Single Factor

	A	B	C	D	E
1	Anova: Single Factor
2
3	SUMMARY
4	Groups	Count	Sum	Average	Variance
5	Low	15	615.0	41.0	64.3
6	Medium	15	690.0	46.0	71.2
7	High	15	750.0	50.0	58.9
8	Very High	15	720.0	48.0	82.4
9
10	ANOVA
11	Source of Variation	SS	df	MS	F
12	Between Groups	735.0	3	245.0	3.508
13	Within Groups	3913.3	56	69.9
14	Total	4648.3	59
15				P-value: 0.020

ANOVA output: F = 3.508, p-value = 0.020 < 0.05. Reject H₀ — at least one fertilizer group has a significantly different mean yield. Note that ANOVA does not tell you which groups differ; use post-hoc tests (e.g., pairwise t-tests with Bonferroni correction) to find out.

Reading the ANOVA Output

Term	Meaning
F statistic	Ratio of between-group to within-group variance
P-value	Probability of this F if all means are equal
Between Groups SS	Variation explained by group membership
Within Groups SS	Unexplained variation (random error)

• p-value < 0.05 → Reject H₀ → at least one group mean differs.
• p-value ≥ 0.05 → No significant difference across groups.

Two-Way ANOVA

Two factors (fertilizer type + irrigation level): Data Analysis → ANOVA: Two-Factor With Replication. This tests main effects of each factor and their interaction.

---

Course Resources These lecture guides accompany Fundamentals of Statistics by Sullivan. All Excel examples use agricultural and economic datasets. Return to the Teaching page to access lecture notes, exams, and quizzes.