Comparing Assemblages

The workflow

Every comparison in this lab runs through the same five steps. Step 2 is the one people skip, and it can change the answer.

1 · AskOne specific research question

2 · CountDecide the unit: items, catalog lots, or presence per test unit

3 · DescribeCross-tabs, percentages, charts, medians

4 · TestCould the difference be chance?

5 · Size itHow big is the difference?

The data

Two sheets, one row per catalog lot. The Count column holds the number of items in each lot.

School	Academic	Industrial	Units with finds
Bethel	781	232	15
Glenns/Dragon	1,265	19	16
Woodville	2,191	656	69
Total	4,237	907

Pick your tool

Question	Data	Tool
How do the mixes differ?	counts	cross-tab, %
Could it be chance?	counts	χ² (chi-square)
How big is it?	counts	Cramér’s V
Sizes or condition?	measurements	median, histogram
Chance? (3+ groups)	measurements	Kruskal-Wallis
Which pair differs?	measurements	Mann-Whitney

Cleaning tip: Layer labels vary ("B " with a trailing space, "A Waterscreen Upper", "Clean-up"). Reduce them to the stratigraphic letter A–D before comparing levels.

Before any test: a table like Betti’s

Betti (2023, Table 6.7) reports raw counts and counts standardized by excavated area. Raw totals mostly reflect how much ground was dug.

ComparesRaw counts vs. counts adjusted for unequal excavation effort

ExampleWoodville’s totals look enormous until you divide by its excavated area

Excavated area, back-calculated from Table 6.7

Betti reports both a count and a standardized count for the same artifacts. Dividing one by the other (×100) recovers the area she excavated at each school — it comes out consistent across every row of her table.

School	Excavated area	Test units
Bethel	312.5 sq ft	15
Glenns/Dragon	331.25 sq ft	16
Woodville	1,593.75 sq ft	69

Type × school, raw and standardized (per 100 sq ft)

	Bethel		Glenns/Dragon		Woodville
	Count	Std.	Count	Std.	Count	Std.
Academic	781	249.9	1,265	381.9	2,191	137.5
Industrial	232	74.2	19	5.7	656	41.2

Read it: by raw count, Woodville dwarfs the other two schools. Standardized, Glenns/Dragon actually has the densest academic assemblage — the raw gap was mostly excavation effort, not what was really deposited there.

Excel SUMIFS

Count for one cell:

=SUMIFS(I:I, A:A, "Bethel", E:E, "Academic")

Standardized (area in a small lookup table, e.g. named range or a cell reference):

=[count cell] / 312.5 * 100

Google Sheets

SUMIFS works the same. Keep the three areas in a small table and pull the right one with a lookup instead of typing the number each time:

=SUMIFS(I:I, A:A, "Bethel", E:E, "Academic") / VLOOKUP("Bethel", AreaTable, 2, FALSE) * 100

What a p-value actually says

Every test on the other tabs ends in a p-value. Before trusting one, know what it does and doesn’t mean.

ComparesNothing by itself — this is the logic every test on this page shares

ExampleWith N ≈ 5,000, almost every comparison here comes out "significant"

The logic

Null hypothesis, H₀: assume there’s no real difference — school made no difference to the mix, or to pencil length. Any pattern you see is just noise. This is the hypothesis the math actually works from.
Alternative hypothesis, H₁: school does make a difference. This is what you suspect going in, but the test never assumes it, computes anything under it, or hands you a probability for it.
p-value: if H₀ were true, how surprising would data this extreme (or more) be, just from random sampling? In plain terms: assuming there really is no difference, how often would random sampling alone throw up a result this far from "no difference" — or farther?
Threshold: p < .05 is a convention for "surprising enough to doubt H₀," not a law of nature.

What it is not

Not the probability the null hypothesis is true.
Not the probability your result happened "by chance" in some general sense.
Not a measure of how big or important the difference is — that’s the effect size (Cramér’s V, ε²), on the next tabs.

Reject H₀ — never "accept H₁"

Small p: you reject H₀. You have not proven H₁ — you've shown the no-difference story is hard to square with the data, nothing more.
Large p: you fail to reject H₀. That is not "accepting H₀" or proving there's no difference; it only means this data didn't give you enough evidence to doubt it.
The asymmetry is built into the math: a p-value is computed by assuming H₀ and asking how surprising the data would be under it. Nothing is ever computed under H₁, so the result can count as evidence against H₀ — it can never be evidence for H₁ in the same direct sense.

How to read it, in words

Example: type × school, χ² = 307.4, df = 2, p < .001

Read it as: “If school really made no difference to the type mix (H₀), a table this lopsided or more so would turn up well under 0.1% of the time by chance alone — surprising enough to reject H₀. Type mix is associated with school.”

Not: “There's a 99.9% chance school affects the mix.” · “This proves school affects the mix.” · “We accept H₁ that school affects the mix.”

Z-score Excel = Sheets

By hand: z = (value − mean) ÷ SD. As a formula:

=(x-mean)/sd =STANDARDIZE(x, mean, sd)

STANDARDIZE takes the raw value, the mean and the SD directly, so you don't have to compute the subtraction yourself.

p-value from a z-score Excel = Sheets

Two-tailed =2*(1-NORM.S.DIST(ABS(z),TRUE)) Upper tail =1-NORM.S.DIST(z,TRUE) Lower tail =NORM.S.DIST(z,TRUE)

NORM.S.DIST(z, TRUE) returns the cumulative probability P(Z ≤ z) — that TRUE is what makes it the running-total curve, not the bell-curve height. Example: z = 1.96 → two-tailed p = .050; z = 2.576 → two-tailed p = .010.

🔔

Try it: Z-Score & P-Value Explorer →

Click anywhere on the curve or type a z-score directly, and watch the shaded p-value area update for a two-tailed, upper-tail, or lower-tail test.

◐

Try it: Type I & Type II Error Explorer →

Drag the decision threshold and the n / effect-size sliders to see why "fail to reject" can mean the test just didn't have the power to detect a real difference.

Why this matters here: with N ≈ 5,000 fragments, almost any real pattern — even a tiny one — will clear p < .05. A significant p-value tells you a difference probably isn’t just noise; it does not tell you the difference is large or archaeologically important. Always report the effect size and the percentages alongside it.

Describe first: the cross-tab

A table of counts with column percentages is the base of every count-based comparison.

ComparesThe make-up of assemblages across groups (schools, levels, trenches)

ExampleIndustrial items are 22.9% at Bethel, 1.5% at Glenns/Dragon, 23.0% at Woodville

	Bethel	Glenns/Dragon	Woodville	Total
Academic	781	1,265	2,191	4,237
Industrial	232	19	656	907
% industrial	22.9%	1.5%	23.0%	17.6%

Excel pivot

Select the data, then Insert → PivotTable.
Rows: Type · Columns: Site · Values: Sum of Count.
Value Field Settings → Show Values As → % of Column Total.
One cell at a time instead:

=SUMIFS(I:I, E:E, "Industrial", A:A, "Bethel")

Google Sheets pivot or QUERY

Insert → Pivot table, same fields.
Summarize by SUM, show as → % of column.
Or build the whole table in one formula:

=QUERY(A:I, "select E, sum(I) where E is not null group by E pivot A", 1)

Columns assume the workbook’s Combined data tab: A = Site, E = Type, F = Form, G = Material, I = Count, J = Pencil length. Chart the percentages as a 100% stacked bar, one bar per school.

χ²: could it be chance?

Compare what you observed with what you’d expect if school made no difference. Edit the observed counts to see every piece update.

ComparesWhether category proportions differ between groups more than chance allows

ExampleType × school: χ² = 307.4, df = 2, p < .001

Observed (editable)

Expected

Residuals: which cells drive it

Positive (copper) = more than expected; negative (blue) = fewer. Beyond ±2 is worth discussing.

The math

Expected count for row i, column j

E_{i j} = \frac{R_{i} \times C_{j}}{N}

R = row total, C = column total, N = grand total

Pearson residual

r_{i j} = \frac{O_{i j} - E_{i j}}{\sqrt{E_{i j}}}

Squaring and summing the residuals gives χ².

χ² statistic

χ^{2} = \sum_{i, j} \frac{{(O_{i j} - E_{i j})}^{2}}{E_{i j}} df = (r - 1) (c - 1)

O = observed, r and c = number of rows and columns

Worked cell: Bethel, academic

E = \frac{4,237 \times 1,013}{5,144} = 834.4 \frac{{(781 - 834.4)}^{2}}{834.4} = 3.42 r = \frac{781 - 834.4}{\sqrt{834.4}} = - 1.85

Do this for all six cells and add the middle terms: χ² = 307.4, df = (2 − 1)(3 − 1) = 2.

⚒

Try it: χ² Builder →

Build your own observed table, pick a shape, and watch expected counts, residuals, χ², df and p update live.

χ² formulas Excel = Sheets

Observed table in B2:D3, row totals in column E, column totals in row 4.
Expected: type in B7, copy across B7:D8=$E2*B$4/$E$4
p-value=CHISQ.TEST(B2:D3, B7:D8)
χ² statistic for your report=SUMPRODUCT((B2:D3-B7:D8)^2/B7:D8)
Residual for each cell=(B2-B7)/SQRT(B7)

Check the assumptions

Expected ≥ 5 in every cell. Check with =MIN(B7:D8). If not, merge categories or use Fisher’s exact test in R.
Independent observations. Fragments of one object aren’t independent. Re-run with catalog lots or presence per unit as a check.
df = (rows − 1) × (columns − 1).
The p-value says the table is unusual. The residuals say where.

Why this distribution is called "chi-square"

Not a metaphor — it's the literal mechanism, one step at a time.

Square a standard normal Z, and you've defined the distribution called "chi-square with 1 degree of freedom."
Add up k independent squared Z's, and you get the χ² distribution with k degrees of freedom.
Our Pearson residuals, (O−E)/√E, behave approximately like a standard normal Z once the sample is reasonably large — that's the Central Limit Theorem at work.
χ² = Σ(O−E)²/E is exactly the sum of those squared residuals — approximately a sum of squared Z's.
So the name "chi-square" for this statistic's reference distribution isn't a metaphor. It's built the same way an actual χ² variable is built.
The table has 6 cells, but the row and column totals are fixed, so only 2 cells are actually free to vary once those totals are set. That's why we compare χ² against the χ² distribution with df = 2, not 6.

Cramér’s V: how big is the difference?

p-values shrink as samples grow; the effect size does not. Scale the sample below while keeping the same proportions.

ComparesThe strength of association in a χ² table, 0 (none) to 1 (complete)

ExampleType × school V = 0.24; pencil material × school V = 0.36

Sample size:

The math

χ²

χ^{2} = \sum_{i, j} \frac{{(O_{i j} - E_{i j})}^{2}}{E_{i j}} E_{i j} = \frac{R_{i} \times C_{j}}{N}

Same χ² from the Chi-square tab. Look at E: N is already sitting in its denominator, so N is baked into χ² before V ever touches it.

Cramér’s V

V = \sqrt{\frac{χ^{2}}{N (k - 1)}} k = min (r, c)

N shows up a second time here, in V’s own denominator. Those two N’s — one hidden inside χ², one explicit — are what cancel each other, and dividing by k − 1 then keeps V between 0 and 1 for any table size.

Why p shrinks but V doesn’t

same proportions, N \times m \Rightarrow χ^{2} \times m, V unchanged

Multiply every count by m and χ² grows by m, so p falls. V cancels the m out.

Worked: type × school

V = \sqrt{\frac{307.4}{5,144 \times (2 - 1)}} = \sqrt{0.0598} = 0.24

Excel and Sheets

=SQRT(chi2 / (N * (MIN(rows, cols) - 1)))

For a 2 × 3 table, MIN(rows, cols) − 1 = 1, so V = √(χ² ÷ N).

Rough reading

V	Strength	Lab example
≈ 0.1	weak
≈ 0.3	moderate	0.24 type × school · 0.28 type × Woodville level
≥ 0.5	strong

Why V doesn't grow with N, even though χ² does

Same mechanism, run in reverse — dividing the sample-size dependence back out.

Keep the same proportions in the table but multiply every cell by m (same shape, more data), and χ² multiplies by m too — more observations just means more terms adding up in the same direction.
That's why p keeps shrinking as N grows: χ² eventually gets big enough to look "significant" even for a tiny, unimportant difference.
Cramér's V divides χ² by N (and by k−1, the largest number of cells that can vary) before taking a square root: V = √(χ² ÷ (N(k−1))).
Dividing by N exactly cancels that m: χ² behaves like N × φ² for some φ that depends only on the proportions in the table, not on how much data you collected.
So φ² = χ²/N is already free of sample size; V is just √(φ²/(k−1)), rescaled to run from 0 to 1.
That's the whole story: χ² (and p) tells you how much data it took to detect the pattern; V tells you how strong the pattern actually is.

Measurements: state of recovery

For lengths, weights or completeness, look at the distribution before choosing a summary or a test.

ComparesThe size or condition of objects across groups

ExampleGraphite pencil fragment length: median 6.8 mm at Bethel, 6.2 at Woodville, 5.5 at Glenns/Dragon

Length distribution

Right-skewed: many short fragments, a few long ones. The median is a fairer summary than the mean.

Summary

School	n	Median	Mean	SD	Range
Bethel	572	6.84	7.59	3.89	2.1–30.0
Glenns/Dragon	53	5.50	6.30	3.17	2.0–16.5
Woodville	315	6.20	6.89	3.26	2.4–24.7

↔

Try it: Mean vs Median — drag an outlier →

Drag one fragment length out to 30 mm and watch the mean chase it while the median barely moves.

Excel

=COUNTIFS(A:A,"Bethel",G:G,"graphite",F:F,"pencil",J:J,"<>") =AVERAGEIFS(J:J,A:A,"Bethel",G:G,"graphite",F:F,"pencil")

Median (Excel 365):

=MEDIAN(FILTER(J:J,(A:A="Bethel")*(G:G="graphite")*(F:F="pencil")*(J:J<>"")))

Older Excel: =MEDIAN(IF(...)), entered with Ctrl+Shift+Enter. Histogram: Insert → Chart → Histogram.

Google Sheets

COUNTIFS and AVERAGEIFS work the same. FILTER takes separate conditions:

=MEDIAN(FILTER(J:J, A:A="Bethel", G:G="graphite", F:F="pencil", J:J<>"")) =STDEV(FILTER(J:J, A:A="Bethel", G:G="graphite", F:F="pencil", J:J<>""))

Histogram: Insert → Chart → Chart type: Histogram.

Kruskal-Wallis and pairwise follow-ups

Neither Excel nor Sheets has a built-in function, but a few formulas do it. It uses ranks instead of raw values, so skew and outliers matter less.

ComparesWhether a measurement’s distribution differs among three or more groups

ExamplePencil fragment length by school: H = 10.2, df = 2, p = .006

The math

Kruskal-Wallis H

H = \frac{12}{N (N + 1)} \sum_{j = 1}^{g} \frac{R_{j}^{2}}{n_{j}} - 3 (N + 1) df = g - 1

R_j = sum of ranks in group j, n_j = its size, g = number of groups. Compare H with a χ² distribution.

Tie correction (optional)

H_{corr} = \frac{H}{1 - \frac{\sum (t^{3} - t)}{N^{3} - N}}

t = number of values sharing each tied rank.

Mann-Whitney U and its z

U_{1} = R_{1} - \frac{n_{1} (n_{1} + 1)}{2}

z = \frac{U_{1} - \frac{n_{1} n_{2}}{2}}{\sqrt{\frac{n_{1} n_{2} (n_{1} + n_{2} + 1)}{12}}}

Two-sided p = 2 × (1 − Φ(|z|)), where Φ is the standard normal curve.

Worked: pencil length, N = 940

School	n	Rank sum R	R² ÷ n
Bethel	572	280,817.0	137,863,964
Glenns/Dragon	53	20,700.5	8,085,108
Woodville	315	140,752.5	62,892,909
Sum	940		≈ 208,841,980

H = \frac{12}{940 \times 941} \times 208,841,980 - 3 \times 941 = 2,833.2 - 2,823 = 10.2

With df = 2, p = .006.

⇅

Try it: From Raw Values to Ranks →

Toggle between raw fragment lengths and their ranks, and watch one huge outlier stop dominating the moment it becomes just "rank 15."

Kruskal-Wallis Excel = Sheets

Put the graphite pencils with a length on their own sheet: A = Site, B = Length (940 rows).

Rank every fragment, shortest = 1 (column C)=RANK.AVG(B2, $B$2:$B$941, 1)
For each school: n and the sum of ranks R=COUNTIF(A:A, "Bethel") =SUMIF(A:A, "Bethel", C:C)
H statistic (N = 940)=12/(N*(N+1)) * SUM(R²/n for each school) - 3*(N+1)
p-value, df = number of schools − 1=CHISQ.DIST.RT(H, 2)

Skipping the tie correction barely changes H with this data.

Which schools differ? Mann-Whitney

Keep two schools only and re-rank them together.
U = R₁ − n₁(n₁+1)/2
z = (U − n₁n₂/2) / √(n₁n₂(n₁+n₂+1)/12)
=2*(1-NORM.S.DIST(ABS(z), TRUE))
Three comparisons, so adjust: multiply p by 3 (Bonferroni) or use Holm.

Pair	p	Holm p
Bethel vs Glenns/Dragon	.012	.036
Bethel vs Woodville	.020	.039
Glenns/Dragon vs Woodville	.135	.135

Bethel’s fragments run a little longer than those at the other two schools.

▦

Try it: Mann-Whitney U, Cell by Cell →

See U built one pairwise comparison at a time, then check it against the rank-sum shortcut.

⇊

Try it: Holm Correction, Step by Step →

Step through sorting, multiplying, and the running max that turns three raw p-values into three honest ones.

The workflow

The data

Pick your tool

What are we counting?

Industrial share of each school

Pearson residuals, industrial row

Before any test: a table like Betti’s

Excavated area, back-calculated from Table 6.7

Type × school, raw and standardized (per 100 sq ft)

Excel SUMIFS

Google Sheets

What a p-value actually says

The logic

What it is not

Reject H0 — never "accept H1"

How to read it, in words

Z-score Excel = Sheets

p-value from a z-score Excel = Sheets

Try it: Z-Score & P-Value Explorer →

Try it: Type I & Type II Error Explorer →

Describe first: the cross-tab

Excel pivot

Google Sheets pivot or QUERY

χ²: could it be chance?

Observed (editable)

Expected

Residuals: which cells drive it

The math

Try it: χ² Builder →

χ² formulas Excel = Sheets

Check the assumptions

Why this distribution is called "chi-square"

Cramér’s V: how big is the difference?

The math

Excel and Sheets

Rough reading

Why V doesn't grow with N, even though χ² does

Measurements: state of recovery

Length distribution

Summary

Try it: Mean vs Median — drag an outlier →

Excel

Google Sheets

Kruskal-Wallis and pairwise follow-ups

The math

Try it: From Raw Values to Ranks →

Kruskal-Wallis Excel = Sheets

Which schools differ? Mann-Whitney

Try it: Mann-Whitney U, Cell by Cell →

Try it: Holm Correction, Step by Step →

Before you trust it, and how to write it

Four checks

A results sentence

Reject H₀ — never "accept H₁"