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. Academic and industrial finds are on separate sheets, so there is no Type column. The sheet is the type. Both sheets use the same columns (B = Site, F = Layer, J = Count, L = Material, M = Form, AF = Pencil length).

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
Could it be chance?	counts
How big is it?	counts
Sizes or condition?	measurements
Chance? (3+ groups)	measurements
Which pair differs?	measurements

Cleaning tip: the raw Layer column (F on both sheets) mixes the four real strata, A (227 rows), B (542), C (125) and D (13), with waterscreen notes stapled onto the same field (656 rows in all, labeled A Waterscreen, A Waterscreen Lower, A Waterscreen Upper, B Waterscreen and C Waterscreen), small variants like "B " with a trailing space, "BII," "C South 1/2" and a misspelled "A Waterscren," and entries that are not one stratum at all, such as "Baulk" (4 rows), "Clean-up"/"Clean up" (3) and "B and C" (1), plus 272 blank cells, mostly finds from features. Put the letter in a new Level column (the first empty one, BN) with =LEFT(TRIM(F2),1) (TRIM drops stray spaces, then LEFT keeps the first character), but first set the Baulk, clean-up, "B and C" and blank rows aside, because the formula would quietly turn Baulk into B and Clean-up into C. It matters. One Baulk lot at Woodville holds 81 jar fragments, and leaving it in level B moves Woodville’s type × level Cramér’s V from 0.33 to 0.28.

Before any test: Betti’s Table 6.7

Betti (2023:281, Table 6.7) counts school-supply artifacts at each school and also standardizes them by excavated area. Raw totals mostly reflect how much ground was dug.

ComparesRaw counts vs. counts adjusted for unequal excavation effort

ExampleWoodville has the most school supplies (2,179) but the lowest density (137 per 100 sq ft)

Table 6.7. School Supply Artifacts from Each School Site

Reproduced from Betti (2023:281). Standardized count = artifacts per square foot excavated × 100.

	Glenns/Dragon		Woodville		Bethel
	Count	Std.	Count	Std.	Count	Std.
Chalk	–	–	8	0.50	28	8.96
Desk	–	–	1	0.63*	9	2.88
Eraser	–	–	3	0.19	1	0.32
Ink Bottle	–	–	9	0.56	6	1.92
Mechanical Pencil or Pen	–	–	–	–	5	1.6
Wooden Pencil	55	16.60	363	22.78	648	207.36
Slate Pencil	11	3.32	85	5.33	–	–
Staple	–	–	–	–	2	0.64
Tack	–	–	–	–	51	16.32
Writing Slate	1190	359.25	1695	106.35	1	0.32
Other	25	7.55	15	0.94	10	3.2
Total	1281	386.72	2179	137.28*	761	243.52

* Every other Woodville row works out to 1,593.75 sq ft, which puts 1 desk at 0.06, not 0.63, most likely a slipped decimal. The Woodville total (137.28) is the sum of the column including that 0.63. Computed directly, 2,179 ÷ 1,593.75 × 100 gives 136.72.

Excavated area, back-calculated from Table 6.7

Count ÷ standardized count × 100 recovers the area Betti excavated at each school. Every row gives the same answer within rounding, apart from the Woodville desk row above.

School	Excavated area	Test units	Worked from
Glenns/Dragon	331.25 sq ft	16	1281 ÷ 386.72 × 100
Woodville	1,593.75 sq ft	69	1695 ÷ 106.35 × 100
Bethel	312.5 sq ft	15	761 ÷ 243.52 × 100

Read it: by raw count, Woodville has the most school supplies. Standardized, it has the fewest per 100 sq ft, and Glenns/Dragon the most, almost all of it writing-slate fragments. The raw gap was mostly excavation effort, not what was deposited.

Academic vs. industrial, per 100 sq ft (Tables 6.7 and 6.12)

Betti’s Table 6.12 does for industrial artifacts (seed beads, jars, pins, thimbles, corset stays) what Table 6.7 does for school supplies. Putting the two totals side by side gives the comparison the rest of this page tests.

School	Academic	per 100 sq ft	Industrial	per 100 sq ft	% industrial
Glenns/Dragon	1281	386.72	18	5.44	1.4%
Woodville	2179	137.28	390	24.47	15.2%
Bethel	761	243.52	197	63.04	20.6%

The lab file gives 1.5%, 23.0% and 22.9% (Cross-tab tab). Glenns/Dragon stands apart either way. Why Woodville differs is explained in the next box. Run χ² on Betti’s totals and Cramér’s V comes out 0.21 instead of 0.24, the same conclusion with a slightly smaller effect.

Lab file vs. Betti’s tables: close, not identical

The rest of this page uses the lab file, because that is what you can reproduce. It comes from the same catalog as Betti’s tables, so the totals should be close, and they are, except for industrial items, mainly at Woodville.

	Glenns/Dragon	Woodville	Bethel
Academic, lab file (Cross-tab)	1,265	2,191	781
School supplies, Table 6.7	1,281	2,179	761
Difference	−16	+12	+20
Industrial, lab file (Cross-tab)	19	656	232
Industrial, Table 6.12	18	390	197
Difference	+1	+266	+35

Academic (under 3% apart): the same finds sorted into slightly different rows. At Bethel the lab file has 20 fewer chalk and 19 more erasers (probably the same objects under different names) and 23 more staples. At Glenns/Dragon, Betti’s “Other” row (25) has no match in the lab file, while the lab file has 7 more writing slates. Her Wooden Pencil row combines pencil leads with pencil bodies, ferrules and erasers (her Tables 6.10–6.11), which the lab file lists under several Material values.

Industrial at Woodville (+266): mostly jar glass (302 fragments in the lab file, 83 in Table 6.12), and two lab-file lots hold 190 of those 302 (109 fragments noted as “mostly if not all from large jar in context” and 81 from the baulk). Most of the rest is beads. The lab file counts every bead, while Betti’s row is labeled seed beads (that is also Bethel’s +35). So a couple of broken jars create the same breakage problem as the slates.

Use the lab-file numbers for your own tests, cite Betti’s when you quote her, and say which one a number comes from.

Excel and Sheets

Worked example: the Lab 3 analysis workbook does exactly this on its Table 6.7 and Table 6.12 sheets.

The same formulas in both. Keep the single quotes around sheet names that contain spaces.

On a new sheet (call it Table 6.7), put school names in B7, D7 and F7 (Glenns/Dragon, Woodville, Bethel, each above its Count column), each school’s excavated area in B5, D5 and F5 (331.25, 1,593.75, 312.5), and the lab file’s Form values in A9 downward (bottle, ink … writing slate/blackboard).

Count: type in B9, then copy to D9 and F9 and down. B$7 picks up the school, $A9 the form:=SUMIFS('Academic Education Artifacts'!$J:$J, 'Academic Education Artifacts'!$B:$B, B$7, 'Academic Education Artifacts'!$M:$M, $A9)
Std. (per 100 sq ft): type in C9, then copy to E9 and G9 and down:=B9/B$5*100
Total, in the row under the last form (row 23 for the 14 academic forms), copied across:=SUM(B9:B22)
Check row (row 24): every row for that school, with no Form condition. It should equal the Total, which means no form was missed:=SUMIFS('Academic Education Artifacts'!$J:$J, 'Academic Education Artifacts'!$B:$B, B$7)
Table 6.12: the same layout on another sheet (Table 6.12), with the Industrial sheet in the formulas and its six forms in A9:A14, so the Total is =SUM(B9:B14):=SUMIFS('Industrial Education Artifacts'!$J:$J, 'Industrial Education Artifacts'!$B:$B, B$7, 'Industrial Education Artifacts'!$M:$M, $A9)

The rows are the lab file’s own Form values (all pencils in one row), so the totals are close to Betti’s but not identical (see the box above).

What the functions do

'Sheet name'!$J:$J

A reference to another sheet. The part before ! is the sheet, the part after is the cells, here all of column J. The single quotes are needed whenever the sheet name has a space in it.

$J:$J B$7 $A9 $E$4

A $ locks the part right after it when you copy the formula. $J:$J stays on column J wherever you copy it. B$7 keeps row 7 (the header row) but lets the column move, so copied right it becomes D$7 and F$7. $A9 keeps column A but lets the row move, so copied down it becomes $A10, $A11. $E$4 never moves.

SUMIFS(sum_range, criteria_range1, criterion1, criteria_range2, criterion2, …)

Adds up one column, but only on the rows where every condition is true. Conditions come in pairs (where to look, what to match), and you can add as many pairs as you need.

'Academic…'!$J:$J sum_range, the column to add up (Count).
'Academic…'!$B:$B criteria_range1, where to check the first condition (Site).
B$7 criterion1, the value Site must equal (the school named in the header).
'Academic…'!$M:$M criteria_range2, where to check the second condition (Form).
$A9 criterion2, the value Form must equal (the form named in column A).

SUMIFS with fewer conditions

Leave out a pair and that condition is dropped. The Check row keeps only the Site pair, so it adds every row for that school whatever its form.

SUM(B9:B22)

Adds every number in the range.

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 items, almost any count comparison here comes out "significant"

The logic

Null hypothesis, H₀: assume there’s no real difference, that 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: the probability of getting a test statistic at least as extreme as the one observed, computed assuming H₀ is true. 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.

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₀, but never evidence for H₁ in the same direct sense.

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 its own tab).

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. That is surprising enough to reject H₀. Type mix is associated with school.” or “Under H₀, a result at least this extreme has less than a 0.1% chance of occurring.”

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.”

🔔

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 the data are hard to explain as noise alone. 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 and Sheets

Worked example: the Lab 3 analysis workbook does exactly this on its Cross-tab sheet.

Build the table on a new sheet (call it Cross-tab). Its counts are already the Total rows of the Table 6.7 and Table 6.12 sheets (Descriptive stats tab), so link to them instead of counting again. The formulas are the same in both. Keep the single quotes around sheet names that contain spaces.

Type the school names in B1:D1 (Bethel, Glenns/Dragon, Woodville), Academic in A2 and Industrial in A3.
Academic row: link B2, C2 and D2 to the Total row (23) of the Table 6.7 sheet. That sheet lists the schools in Betti’s order (Glenns/Dragon, Woodville, Bethel), so each cell gets its own link='Table 6.7'!F23 ='Table 6.7'!B23 ='Table 6.7'!D23
Industrial row: link B3, C3 and D3 to the Total row (15) of the Table 6.12 sheet='Table 6.12'!F15 ='Table 6.12'!B15 ='Table 6.12'!D15No Table sheets? Sum the Count column straight from the lab sheet instead. Type in B2 and copy across to D2, then use the Industrial sheet for row 3:=SUMIFS('Academic Education Artifacts'!$J:$J, 'Academic Education Artifacts'!$B:$B, B$1)
Totals: =SUM(B2:D2) in E2 (copy to E3), =SUM(B2:B3) in B4 (copy to E4).
% industrial: =B3/B4 in B5, copy to E5, and format row 5 as a percentage.

Or with two pivot tables

The workbook’s Pivot table method sheet has these steps too, with the numbers each pivot should show.

One pivot per lab sheet, then combine their results on the Cross-tab sheet.

On the Academic sheet, insert a pivot table. Put Site in Columns and Count in Values, summarized by SUM. With Site in Columns the three schools come out side by side, ready to drop into a row of the table. In Rows they would come out stacked in a column.
Do the same on the Industrial sheet.
Pivots sort the schools alphabetically (Bethel, Glenns/Dragon, Woodville), the same order as the table above.
On the Cross-tab sheet, link B2:D2 to the Academic pivot’s three totals and B3:D3 to the Industrial pivot’s. Type = and click the pivot cell. Link rather than paste values, so the table updates with the data.
Leave the pivots’ Grand Total behind. Work out the totals and % industrial on the Cross-tab sheet as in steps 4–5 of the formula method above, and keep the counts in B2:D3 so the χ² formulas on the next tab work unchanged.

Excel

Pivot: Insert → PivotTable.
Clicking a pivot cell writes a GETPIVOTDATA formula, which keeps finding the right value even if the pivot’s layout changes.
After you change the lab data, use Data → Refresh All.
Percent: Home → Percent Style (%).

Sheets

Pivot: Insert → Pivot table.
Clicking a pivot cell writes an ordinary reference to the pivot’s sheet.
Pivots update on their own when the data changes.
Percent: Format → Number → Percent.

What the functions do

='Table 6.7'!F23

A plain link. The cell shows whatever is in F23 on the Table 6.7 sheet and updates when that cell changes, so the count is worked out in one place only.

SUMIFS(sum_range, criteria_range1, criterion1)

The no-Table-sheets version needs only one condition, the school.

'Academic…'!$J:$J sum_range, the column to add up (Count).
'Academic…'!$B:$B criteria_range1, where to check the condition (Site).
B$1 criterion1, the school named in row 1. Row 1 is locked, so copied right it becomes C$1 and D$1.

GETPIVOTDATA(data_field, pivot_table, field1, item1, …)

What Excel writes when you click a pivot cell. It asks the pivot for a value by name (for example the sum of Count where Site is Bethel) instead of by cell address, so it still finds the right number if the pivot is rearranged. pivot_table is any cell inside the pivot.

Both lab sheets use the same columns (B = Site, F = Layer, J = Count, L = Material, M = Form, AF = 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} = C_{j} \times \frac{R_{i}}{N}

C = column total (that school’s items) · R ÷ N = the row’s share of the grand total. If school made no difference, each school gets the overall share of its own items.

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}}

O = observed. Compare it with a χ² distribution with df = (r − 1)(c − 1), where r and c are the numbers of rows and columns.

Worked: one cell in full (Bethel, academic)

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

All six cells

Cell	O	E = C × (R ÷ N)	O − E	Residual r = (O − E) ÷ √E	r² = (O − E)² ÷ E
Bethel, academic	781	1,013 × 0.8237 = 834.4	−53.4	−1.85	3.42
Glenns/Dragon, academic	1,265	1,284 × 0.8237 = 1,057.6	+207.4	+6.38	40.67
Woodville, academic	2,191	2,847 × 0.8237 = 2,345.0	−154.0	−3.18	10.11
Bethel, industrial	232	1,013 × 0.1763 = 178.6	+53.4	+3.99	15.96
Glenns/Dragon, industrial	19	1,284 × 0.1763 = 226.4	−207.4	−13.78	189.99
Woodville, industrial	656	2,847 × 0.1763 = 502.0	+154.0	+6.87	47.25
Sum	5,144	5,144.0	0	−1.57	307.40 = χ²

χ^{2} = 3.42 + 40.67 + 10.11 + 15.96 + 189.99 + 47.25 = 307.4 df = (2 - 1) (3 - 1) = 2

0.8237 = 4,237 ÷ 5,144 (academic share of the whole table) and 0.1763 = 907 ÷ 5,144 (industrial share). The O − E column cancels to 0 down each school, because observed and expected share the same totals. That is one reason only 2 of the 6 cells are free. The residuals do not cancel (they sum to −1.57) because each gap is divided by a different √E. Their sum means nothing on its own. What χ² adds up is their squares, in the last column, (−1.85)² = 3.42 and so on, so the squares sum to 307.4. Glenns/Dragon’s industrial cell alone supplies 190 of the 307.4. With df = 2, p = CHISQ.DIST.RT(307.4, 2) ≈ 1.8 × 10⁻⁶⁷, reported as p < .001.

⚒

Try it: χ² Builder →

Draw standard-normal Z’s, square them and stack them, and watch the χ² distribution for df 1–6 take shape. It is the picture behind the p-value on this tab.

Excel and Sheets

Worked example: the Lab 3 analysis workbook does exactly this on its Chi-square sheet.

The same formulas in both. On a new sheet (call it Chi-square):

Observed table in B2:E4, linked from the Cross-tab sheet, with counts in B2:D3, row totals in column E and column totals in row 4. Type in B2, copy across B2:E4='Cross-tab'!B2
Expected: type in B7, copy across B7:D8=B$4*($E2/$E$4)
Residual: type in B11, copy across B11:D12=(B2-B7)/SQRT(B7)
Residual squared, (O − E)² ÷ E: type in B15, copy across B15:D16=(B2-B7)^2/B7
χ² in B20, df in B21=SUM(B15:D16) =(ROWS(B2:D3)-1)*(COLUMNS(B2:D3)-1)
p in B22, and in B23 the way to write it in a report=CHISQ.DIST.RT(B20,B21) =IF(B22<0.001,"p < .001",TEXT(B22,"0.000"))
Cross-check: the same p in one step=CHISQ.TEST(B2:D3,B7:D8)

What the functions do

=B$4*($E2/$E$4)

Expected count, column total × (row total ÷ N). B$4 is the school’s column total (row locked, so copied right it becomes C$4, D$4). $E2 is the row total (column locked, so copied down it becomes $E3). $E$4 is N and never moves.

SQRT(number)

Square root.

ROWS(range) COLUMNS(range)

Count the rows and the columns of a range. For B2:D3 they give 2 and 3, so df = (2 − 1) × (3 − 1) = 2 comes from the table’s shape instead of being typed in.

CHISQ.DIST.RT(x, deg_freedom)

The right-tail area of the χ² distribution, the chance of a χ² at least as large as x when H₀ is true. That area is the p-value.

B20 x, the χ² you calculated.
B21 deg_freedom, the df.

IF(test, value_if_true, value_if_false) TEXT(value, format)

IF checks the test and returns one of two results. Here it writes “p < .001” when p is tiny, and otherwise uses TEXT to turn the number into text with three decimals (the format "0.000").

CHISQ.TEST(actual_range, expected_range)

Takes the observed table and the expected table (same shape), works out χ² and df on its own, and returns the p-value directly. Useful as a check on the step-by-step result.

MIN(range)

The smallest number in the range. On the expected table it tells you at once whether any expected count is under 5.

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 normal approximation to counts, a consequence of the Central Limit Theorem.
χ² = Σ(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

In one line: p tells you whether, V tells you how much. The χ² test asks whether the pattern is too big to be chance. Cramér’s V says how strong it is. Report both, side by side.

Sample size: N =

Drag the slider (50 to 50,000), or type any N from 10 to 1,000,000.

The math

χ²

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

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

Same χ² from the Chi-square tab. Its size depends on N as well as on the pattern. With the same proportions, double every count and χ² doubles.

Cramér’s V

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

Dividing by N takes out the part of χ² that comes from sample size, and dividing by k − 1 then keeps V between 0 and 1 for any table size.

Why p shrinks but V doesn’t

N \times m \Rightarrow χ^{2} \times m

V unchanged

Keep the same proportions and multiply every count by m, and each gap O − E grows m-fold while √E grows only √m-fold, so χ² grows by m and p falls. V divides by N, which also grew by m, so the m cancels.

Worked: type × school

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

Rough reading

V	Strength	Lab example
≈ 0.1	weak
≈ 0.3	moderate	0.24 type × school · 0.33 type × Woodville level (A–C)
≥ 0.5	strong

Excel and Sheets

Worked example: the Lab 3 analysis workbook does exactly this on its Cramérs V sheet.

The same formulas in both. On a new sheet next to the Chi-square sheet:

χ² in B4 and N in B5, from the Chi-square sheet='Chi-square'!B20 ='Chi-square'!E4
Rows in B6, columns in B7, and k = min(r, c) in B8=ROWS('Chi-square'!B2:D3) =COLUMNS('Chi-square'!B2:D3) =MIN(B6,B7)
Cramér’s V in B10=SQRT(B4/(B5*(B8-1)))
Rough reading in B11 (cut points at 0.2 and 0.5, a rough convention)=IF(B10>=0.5,"strong",IF(B10>=0.2,"moderate","weak"))

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

What the functions do

ROWS(range) COLUMNS(range)

Count the rows and the columns of a range. For the observed table (B2:D3 on the Chi-square sheet) they give r = 2 and c = 3.

MIN(B6,B7)

The smaller of the number of rows and columns, which is k in the V formula.

SQRT(number)

Square root.

IF(B10>=0.5,"strong",IF(B10>=0.2,"moderate","weak"))

An IF inside an IF. It checks ≥ 0.5 first, and only if that fails does it check ≥ 0.2. Anything below both is “weak”. The order matters, since 0.6 also passes the second test.

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

Same mechanism, run in reverse. It divides the sample-size dependence back out.

Keep the same proportions in the table but multiply every cell by m (same shape, more data). Every gap O − E grows m-fold, while the chance wobble √E grows only √m-fold, so each cell’s contribution, and χ² with it, grows m-fold. Still six cells, each one just counting for more.
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 value χ²/N can reach) 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, with many short fragments and 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 with an Outlier →

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

Convert the lengths first. In the lab file, 934 of the 940 graphite pencil lengths in column AF are stored as text ("10.29", not 10.29). AVERAGEIFS, MEDIAN, STDEV and RANK.AVG skip text without warning, so you get a wrong number or an error. How to convert them is in the Excel and Sheets box below.

Excel and Sheets

Graphite pencils are all on the Academic sheet. Type these in an empty column to the right of the data on that sheet (B = Site, L = Material, M = Form, AF = Pencil length). Count and mean are the same in both:

=COUNTIFS(B:B,"Bethel",L:L,"graphite",M:M,"pencil",AF:AF,"<>") =AVERAGEIFS(AF:AF,B:B,"Bethel",L:L,"graphite",M:M,"pencil")

Excel

Lengths to numbers: select column AF, then Data → Text to Columns → Finish.

Median and SD (Excel 365, where FILTER multiplies the conditions together):

=MEDIAN(FILTER(AF:AF,(B:B="Bethel")*(L:L="graphite")*(M:M="pencil")*(AF:AF<>""))) =STDEV.S(FILTER(AF:AF,(B:B="Bethel")*(L:L="graphite")*(M:M="pencil")*(AF:AF<>"")))

Older Excel without FILTER: =MEDIAN(IF(...)), entered with Ctrl+Shift+Enter.

Histogram: Insert → Chart → Histogram.

Sheets

Lengths to numbers: select column AF, then Data → Split text to columns.

Median and SD (FILTER takes the conditions as separate arguments):

=MEDIAN(FILTER(AF:AF, B:B="Bethel", L:L="graphite", M:M="pencil", AF:AF<>"")) =STDEV(FILTER(AF:AF, B:B="Bethel", L:L="graphite", M:M="pencil", AF:AF<>""))

Histogram: Insert → Chart → Chart type: Histogram.

If a length still sits left-aligned in its cell after converting, it is still text. A helper column with =VALUE(AF2) converts it in either program.

What the functions do

COUNTIFS(criteria_range1, criterion1, …)

Counts the rows where every condition is true. Same condition pairs as SUMIFS, but with no column to add. It only counts rows. The condition "<>" means “not empty” (<> is “not equal to”, followed by nothing).

AVERAGEIFS(average_range, criteria_range1, criterion1, …)

The mean of one column over the rows where every condition is true. Same order as SUMIFS, with the column to average first. Cells holding text are skipped, so convert the lengths to numbers first.

FILTER(range, conditions)

Returns only the values from the range on rows that meet the conditions, as a list that MEDIAN or STDEV can use. Excel takes one combined condition, and each test gives 1 (true) or 0 (false), so multiplying them with * gives 1 only where all are true. Sheets takes each condition as its own argument instead.

MEDIAN(…) STDEV.S(…) STDEV(…)

The middle value, and the sample standard deviation (dividing by n − 1). STDEV in Sheets is the same calculation as STDEV.S in Excel.

MEDIAN(IF(...))

The pre-365 workaround. IF returns the length on rows that meet the conditions and FALSE elsewhere, MEDIAN ignores the FALSEs, and Ctrl+Shift+Enter tells older Excel to run IF on every row at once.

VALUE(text)

Turns text that looks like a number, such as "6.8", into the number 6.8.

Kruskal-Wallis and pairwise follow-ups

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

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."

Excel and Sheets

Every formula below works the same in both.

Kruskal-Wallis

First convert the lengths to numbers (see the Measurements tab), then make a sheet of just the graphite pencils that have a length. On the Academic sheet, filter Material (L) = graphite, Form (M) = pencil and Pencil Length (AF) not blank, then copy Site (B) and Pencil Length (AF) into a new sheet as columns A and B, with headers in row 1 (940 rows).

Rank every fragment, shortest = 1 (column C)=RANK.AVG(B2, $B$2:$B$941, 1)
Type the school names in E2:E4. Then n in F2 and the rank sum R in G2, copied down to row 4=COUNTIF(A:A, E2) =SUMIF(A:A, E2, C:C)
Total N in F5 (940), then H in F6=SUM(F2:F4) =12/(F5*(F5+1))*SUMPRODUCT(G2:G4^2/F2:F4)-3*(F5+1)
p-value in F7, df = number of schools − 1=CHISQ.DIST.RT(F6, 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), in a cell of its own (say J2)
p-value from z=2*(1-NORM.S.DIST(ABS(J2), TRUE))NORM.S.DIST(z, TRUE) is the cumulative probability P(Z ≤ z). 1 minus it is the upper tail, and times 2 gives the two-tailed p. Example: z = 1.96 → p = .050.
Three comparisons, so adjust. Multiply p by 3 (Bonferroni) or use Holm.

Pair	p	Holm p
Bethel vs Glenns/Dragon	.0119	.0357
Bethel vs Woodville	.0195	.0390
Glenns/Dragon vs Woodville	.1346	.1346

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

What the functions do

RANK.AVG(number, ref, order)

The rank of one value within a list.

B2 number, this fragment’s length.
$B$2:$B$941 ref, all 940 lengths, locked with $ so the list stays put when you copy the formula down.
1 order. 1 ranks from smallest (shortest = 1), and 0 ranks from largest. Tied values share the average of the ranks they span, so two fragments tied for 4th and 5th both get 4.5.

COUNTIF(range, criterion) SUMIF(range, criterion, sum_range)

The one-condition versions. COUNTIF(A:A, E2) counts the rows whose school is E2. SUMIF(A:A, E2, C:C) adds column C (the ranks) on those rows. Note that SUMIF puts the column to add last, while SUMIFS puts it first.

SUMPRODUCT(G2:G4^2/F2:F4)

Works cell by cell, then adds. For each school it squares the rank sum (G) and divides by n (F), and then adds the three results, which is the Σ R²/n part of the H formula.

CHISQ.DIST.RT(F6, 2)

Same function as on the χ² tab. H is compared with the χ² distribution with df = number of schools − 1 = 2.

NORM.S.DIST(z, cumulative)

The standard normal distribution. With TRUE it returns P(Z ≤ z), the area to the left of z. With FALSE it returns the height of the curve, which is not a probability.

ABS(J2) removes the sign, so a negative z is treated like the same distance on the positive side.
1 − NORM.S.DIST(…) the area beyond |z| in one tail.
2 * (…) both tails, the two-tailed p.

▦

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.

Before you trust it, and how to write it

Four checks

Independence: fragments of one slate are not separate observations. Try catalog lots or presence per test unit.
Expected ≥ 5: small subsets can fall below it quickly. Merge categories or use Fisher’s exact test.
Excavation effort: finds come from 69 units at Woodville and 15 at Bethel. Compare proportions, not totals.
Many tests: run 20 at p < .05 and about one will look "significant" by chance alone. Ask one main question, or correct for multiple tests (Holm, on the Kruskal-Wallis tab).

A results sentence

“Industrial items made up 23% of the Woodville assemblage but 1.5% at Glenns/Dragon (χ² = 307.4, df = 2, p < .001, Cramér’s V = 0.24). Breakage cuts both ways. Most Glenns/Dragon academic items are slate fragments, and two broken jars supply 190 of Woodville’s 656 industrial items, so part of this gap may reflect breakage rather than teaching.”

Include: the percentages, the test and its numbers, the effect size, and a caveat about what the data can’t tell you.

A second example: big effect, wrong explanation?

Wooden pencils vs. writing slates, from the lab file (wooden pencil = Form “pencil” except slate pencils, writing slate = Form “writing slate/blackboard”):

	Bethel	Glenns/Dragon	Woodville
Wooden pencil	650	54	366
Writing slate	2	1,197	1,710
% writing slate	0.3%	95.7%	82.4%

“Writing slates made up 96% of these finds at Glenns/Dragon and 82% at Woodville but under 1% at Bethel (χ² = 2,172.6, df = 2, p < .001, Cramér’s V = 0.74). Slates fell out of use around 1920 (Betti 2023:281), so this gap may say more about when each school was in use than about what was taught there.”

A large V tells you the pattern is strong, not what caused it. Slate fragments also break the independence check harder than anything else in the data. Betti’s Table 6.7 gives almost the same result (V = 0.74), because these two categories line up cleanly between her table and the lab file.

Other examples to try: slate vs. graphite pencils by school (V = 0.36, and Bethel has none of the 98 slate pencils), or academic vs. industrial by level at Woodville (industrial share falls from 43% in level A to 11% in level C).

The workflow

The data

Pick your tool

What are we counting?

Industrial share of each school

Pearson residuals, industrial row

Before any test: Betti’s Table 6.7

Table 6.7. School Supply Artifacts from Each School Site

Excavated area, back-calculated from Table 6.7

Academic vs. industrial, per 100 sq ft (Tables 6.7 and 6.12)

Lab file vs. Betti’s tables: close, not identical

Excel and Sheets

What a p-value actually says

The logic

Reject H0, never "accept H1"

What it is not

How to read it, in words

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

Try it: Type I & Type II Error Explorer →

Describe first: the cross-tab

Excel and Sheets

Or with two pivot tables

Excel

Sheets

χ²: could it be chance?

Observed (editable)

Expected

Residuals: which cells drive it

The math

Try it: χ² Builder →

Excel and Sheets

Check the assumptions

Why this distribution is called "chi-square"

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

The math

Rough reading

Excel and Sheets

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

Measurements: state of recovery

Length distribution

Summary

Try it: Mean vs Median with an Outlier →

Excel and Sheets

Excel

Sheets

Kruskal-Wallis and pairwise follow-ups

The math

Try it: From Raw Values to Ranks →

Excel and Sheets

Kruskal-Wallis

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

A second example: big effect, wrong explanation?

Reject H₀, never "accept H₁"