Drawing Bootstrap Replicates in a Spreadsheet

From Observed Rows to Bootstrap Replicates

A bootstrap analysis can involve hundreds or thousands of resamples. Drawing them by hand would be slow and error-prone, but a spreadsheet can automate the repeated steps: select rows, calculate a statistic, and record the result. The spreadsheet does not change what a bootstrap sample is; it carries out the same resampling operation consistently.

The key is to sample rows, not isolated cells, whenever several measurements belong to the same sampling unit. If a row contains a person's treatment group and outcome, or a study participant's $x$- and $y$-values, those values must travel together into each resample. Resampling the columns independently would create combinations that were not observed and would break the relationships in the data.

Definition: Given $n$ observed sampling units, one ordinary bootstrap replicate is a sample of $n$ rows drawn independently from the observed rows, with replacement. A statistic is recalculated from those $n$ selected rows. “With replacement” means that a row can be selected more than once, while another row may not be selected at all.

For the ordinary row bootstrap, the observed rows are treated as the empirical distribution: each row has selection probability $1/n$ on every draw. The method is appropriate when the rows represent independent sampling units and the statistic can be calculated from a resample. For paired measurements, the pair is the sampling unit and the entire row is drawn. Other sampling designs, such as clustered or time-ordered data, may require a resampling scheme that reflects that design.

A Spreadsheet Layout That Can Be Checked

Keep the observed data in a clearly labelled source table, with one row per sampling unit and no blank rows within the data range. Add an ID column if it helps identify source rows, but do not include the ID as a measurement in the statistic. A useful resampling table records, for each replicate, the selected source-row indices, the values retrieved from those rows, and the statistic calculated from them.

Suppose the source data occupy rows 2 through $n+1$. A random integer from 1 to $n$ can identify a source row by its position within that data range. In Excel or Google Sheets, `RANDBETWEEN(1,n)` generates such an integer. If the chosen index is stored in a cell, `INDEX` retrieves the corresponding value from a source column. For instance, if the source measurements are in `A2:A6` and the selected index is in `D2`, the formula `=INDEX($A$2:$A$6,D2)` returns the selected measurement. The dollar signs keep the source range fixed when the formula is copied.

A convenient layout for a one-variable statistic uses one spreadsheet row per replicate. Put the $n$ selected indices in adjacent cells, the $n$ retrieved measurements in the next group of cells, and the statistic in a final cell. For multivariable data, retrieve every variable using the same index for a given draw. That shared index is what keeps the row intact.

Keep the source data fixed.
Label variables, confirm which rows are eligible, and record the sample size $n$.

Generate $n$ row indices.
Each index is chosen from 1 through $n$, independently and with replacement.

Retrieve complete rows.
Use each selected index to look up all measurements for that sampling unit.

Calculate and record the statistic.
Apply the same statistic to the selected rows and save one result for this replicate.

Repeat and preserve the draws.
Copy the replicate formulas for the desired number of replicates, then save the generated indices or sampled rows.

The indices make the procedure auditable: a reader can trace any sampled value back to the source row. They also make it possible to regenerate the same resamples even if the spreadsheet's random-number formulas recalculate.

Worked Example: Bootstrap Replicates of a Sample Mean

Suppose a hypothetical sample records the number of hours studied by five students. The observed values, in source-row order, are 2, 4, 6, 8, and 10 hours. We will make one bootstrap replicate using the index draws 1, 2, 2, 5, and 3. These indices are illustrative; a spreadsheet would generate them at random.

The sample size is $n=5$, so the replicate must contain five draws. The indices select the values 2, 4, 4, 10, and 6 hours. Index 2 appears twice, and index 4 does not appear; that is allowed because the draws are with replacement.

The replicate mean is calculated from the selected values:

\bar{x}^{*} = \frac{2+4+4+10+6}{5} = \frac{26}{5} = 5.2 \text{ hours}.

In a spreadsheet, if the five source measurements are in `A2:A6` and the five indices are in `D2:H2`, the retrieved values can be placed in `I2:M2`. For example, `=INDEX($A$2:$A$6,D2)` in `I2` retrieves the first selected value. The mean in `N2` can then be calculated with `=AVERAGE(I2:M2)`. Copying the index, lookup, and mean formulas to another replicate row produces a new set of draws and a new mean.

This one result is a single bootstrap replicate, not a conclusion about the population by itself. In a full spreadsheet, each replicate's mean would be stored on its own row so all results remain available for later analysis.

Preserve Pairs When Resampling a Regression Slope

When the statistic describes a relationship between two measurements, each draw selects an observed pair. The same row index retrieves both coordinates. The slope for a resample is calculated in the usual way, using the resampled values:

b_1^{*} = \frac{\sum_{i=1}^{n}(x_i^{*}-\bar{x}^{*})(y_i^{*}-\bar{y}^{*})} {\sum_{i=1}^{n}(x_i^{*}-\bar{x}^{*})^2}.

Here $x_i^{*}$ and $y_i^{*}$ are the $i$th selected pair in the replicate, and $\bar{x}^{*}$ and $\bar{y}^{*}$ are the means of the selected $x$- and $y$-values. This is the sample regression slope calculated from the bootstrap rows. A spreadsheet's `SLOPE` function can calculate it, provided the selected $x$- and $y$-values occupy matching ranges.

Worked Example: A Bootstrap Replicate of a Regression Slope

Consider hypothetical data relating study hours $x$ to a quiz score $y$, measured in score points:

Source index	Study hours $x$	Quiz score $y$
1	1	2
2	3	5
3	4	6
4	5	7

Suppose one replicate draws indices 1, 2, 2, and 3. The selected pairs are $(1,2)$, $(3,5)$, $(3,5)$, and $(4,6)$. Each selected pair came from a complete observed row. The replicate means are

\bar{x}^{*}=\frac{1+3+3+4}{4}=\frac{11}{4}=2.75, \qquad \bar{y}^{*}=\frac{2+5+5+6}{4}=\frac{18}{4}=4.5.

The numerator of the slope formula is the sum of the four cross-products:

\begin{aligned} \sum (x_i^{*}-\bar{x}^{*})(y_i^{*}-\bar{y}^{*}) &=(1-2.75)(2-4.5)\\ &\quad +(3-2.75)(5-4.5)+(3-2.75)(5-4.5)\\ &\quad +(4-2.75)(6-4.5)\\ &=4.375+0.125+0.125+1.875\\ &=6.5. \end{aligned}

The denominator is

\begin{aligned} \sum (x_i^{*}-\bar{x}^{*})^2 &=(1-2.75)^2+(3-2.75)^2+(3-2.75)^2+(4-2.75)^2\\ &=3.0625+0.0625+0.0625+1.5625\\ &=4.75. \end{aligned}

Therefore, the bootstrap slope is

b_1^{*}=\frac{6.5}{4.75}\approx 1.368 \text{ score points per study hour}.

For the spreadsheet, put the four $x$-values in one source column and the four $y$-values in the next. Generate four indices for this replicate. Use each index to retrieve one $x$-value and the $y$-value from the same source row, then calculate the slope with `=SLOPE(selected_y_range,selected_x_range)`. Do not generate separate index sets for the two columns.

Replicates for a Categorical Statistic

The same row-selection method also works when a row records a category rather than a numerical measurement. For a binary outcome, one useful statistic is the sample proportion with the outcome of interest. Each replicate must again contain $n$ selected rows, and the proportion is calculated from the number of selected rows with that outcome.

Worked Example: A Bootstrap Replicate of a Proportion

Imagine a hypothetical sample of six patients, each with a recorded Yes or No response to a question. In source-row order, the responses are No, Yes, Yes, No, Yes, and No. Consider the index draws 2, 3, 3, 6, 1, and 5.

Looking up those six indices gives Yes, Yes, Yes, No, No, and Yes. Four of the six selected rows have a Yes response, so the replicate proportion is

\hat{p}^{*} = \frac{\text{number of Yes responses in the replicate}}{\text{number of selected rows}} = \frac{4}{6} \approx 0.667.

This is a proportion of about 66.7% for this bootstrap replicate. In the spreadsheet, use the same index cells to retrieve the response for each draw, then count the Yes values and divide by six. A formula such as `=COUNTIF(selected_response_range,"Yes")/6` computes the statistic if the selected responses are text labels.

Why the Spreadsheet Draws Must Be With Replacement

The replacement rule is not a minor spreadsheet setting: it defines the bootstrap replicate. There are $n$ possible source rows on each draw, regardless of which rows were selected earlier in that replicate. Thus, for any specified ordered sequence of $n$ row indices, the probability of that sequence under the bootstrap mechanism is

\left(\frac{1}{n}\right) \left(\frac{1}{n}\right)\cdots \left(\frac{1}{n}\right) = \left(\frac{1}{n}\right)^n.

Each factor is $1/n$, because every draw selects uniformly from all $n$ source rows. If a spreadsheet instead forces every source row to appear exactly once in each replicate, it has produced the original data rearranged, not a bootstrap resample. Duplicates and omissions are expected features of the procedure, not errors to remove.

For a quick quality check, inspect a few replicate rows. Confirm that each has $n$ indices between 1 and $n$, that repeated indices produce repeated source values, and that all measurements from a selected row stay together. Confirm also that the statistic formula refers to the selected data, not accidentally to the original source range.

Recalculation, Saving, and Common Mistakes

Spreadsheet random-number functions commonly recalculate when a workbook changes or is reopened. As a result, the displayed indices and statistics may change even when no resampling formulas were edited. This behavior is useful while generating draws, but it can make an analysis difficult to audit if the draws are not saved.

A practical record includes the original data, the number of rows $n$, the statistic being calculated, the spreadsheet formulas or method, and the actual selected indices for each replicate. Once the desired replicates have been generated, copy the index and statistic cells and paste them as values into a clearly labelled results sheet. If exact rerunning from a random seed is important, use a random-number generator that supports a documented seed and retain that seed and the software details. Merely keeping volatile `RANDBETWEEN` formulas may not reproduce the same draws later.

Common mistakes include:

Sampling without replacement. Restricting each source row to one selection per replicate prevents repeats. Generate every draw independently from the full range of row indices instead.
Resampling columns separately. Independent $x$- and $y$-indices break observed pairs. Use a single index per draw to retrieve all values in that row.
Using the wrong replicate size. An ordinary bootstrap replicate has the same number of rows as the observed sample, $n$. Check the number of index draws before calculating its statistic.
Leaving random formulas volatile. A result that changes on recalculation is not a fixed record of the earlier replicate. Save the actual indices or sampled values as values.
Including identifiers in the statistic. Row numbers and participant IDs help trace observations but are not measurements to average or analyze as outcomes.

The spreadsheet's job in this tutorial is to generate and document replicates correctly. The collection of replicate statistics can be examined in later analyses, but one replicate alone is not an interval, a test, or a final inferential conclusion.

Key takeaway: Generate $n$ independent row indices from 1 through $n$ for each replicate, use each index to retrieve the complete source row, calculate the statistic from those selected rows, and preserve the indices and results. Correct row-level resampling is the foundation of a trustworthy spreadsheet bootstrap.

Check Your Understanding

Answer each question by referring to the resampling procedure and spreadsheet workflow.

A data set has 12 rows. How many row indices should an ordinary bootstrap replicate contain, and what are the possible index values?
In a resample of four paired observations, an index is drawn twice. What should the spreadsheet do with both measurements in that source row?
Explain why selecting each source row exactly once does not produce a bootstrap replicate.
A spreadsheet calculates a mean from the original source column rather than the sampled-value cells. What has gone wrong?
Why might saving only volatile random-number formulas fail to preserve the replicates that were previously calculated?