📊 Google Sheets Handbook

A step-by-step technical guide to statistical analysis and modelling for the Internal Assessment.

How to use this guide. Examples assume your raw data runs from A2:A101 (column A, rows 2–101). Adjust the range to match your own dataset. All formulas start with = and are typed directly into a cell.

Part 1 Descriptive Statistics & Data Representation

1 Summary Statistics Formulas

For any single-variable dataset in A2:A101, enter these formulas into separate summary cells. Build a labelled table in your IA to present them — don't just paste the Sheets output.

Core descriptive statistics

Mean (average)=AVERAGE(A2:A101)

Median=MEDIAN(A2:A101)

Mode=MODE(A2:A101)

Sample standard deviation=STDEV.S(A2:A101)

Population standard deviation=STDEV.P(A2:A101)

Lower quartile Q₁=QUARTILE(A2:A101, 1)

Upper quartile Q₃=QUARTILE(A2:A101, 3)

Interquartile range (IQR)=Cell_Q3 - Cell_Q1 (reference your Q₃ and Q₁ cells)

STDEV.S vs STDEV.P

Use STDEV.S for sampled IA data (almost always the right choice — your data is a sample drawn from a larger population). Use STDEV.P only if your dataset represents the entire population, e.g. every student in one specific class where that class is the whole population of interest.

MODE limitations

=MODE returns only the first mode if multiple exist. For multimodal data use =MODE.MULT(A2:A101) as an array formula (Ctrl+Shift+Enter). If your data is continuous, mode is often not meaningful — focus on mean, median and standard deviation instead.

Criterion E tip — don't just list numbers. In your write-up, explicitly compare the mean and median. If they differ significantly, identify skewness in your data and explain how outliers pulled the mean away from the centre. For example: "The mean (47.3) exceeds the median (41.0), suggesting positive skew — a small number of high-value observations inflate the average." This kind of interpretation is what separates 5/6 from 6/6 on Use of Mathematics.

2 Generating Histograms with Specified Bucket Sizes

Histograms display the frequency distribution of continuous data. Setting a uniform bucket (class interval) width is important for IB presentation standards.

Highlight your data column

Select your continuous dataset including the header row (e.g. click the column A header to select the whole column, or drag from A1 to A101).

Insert the chart

Navigate to Insert → Chart. In the Chart Editor panel that appears on the right, open the Chart type dropdown and select Histogram chart.

Set a custom bucket size

Click the Customize tab inside the Chart Editor, expand the Histogram section, and change Bucket size from "Auto" to your preferred uniform class interval — for example 5.0 or 10.0. Equal-width intervals are required for a valid frequency histogram in an IB context.

Label axes and add a title

Still in Customize → Chart & axis titles: set the chart title, horizontal axis label (with units), and vertical axis label ("Frequency"). An unlabelled histogram cannot earn full marks on Criterion B.

Chart Editor → Customize → Histogram

SettingValue

Bucket size5.0

Show item dividers✓ checked

Outlier percentile0% (show all data)

Part 2 Bivariate Data & Statistical Correlation

1 Plotting a Scatter Diagram with a Trendline & R² Value

To analyse the relationship between two variables, place your independent variable (X) in the left column and your dependent variable (Y) in the adjacent right column. Sheets reads the left column as x by default.

Highlight both data columns uniformly

Select from A1 to B101 (including headers). Both columns must be the same height — any missing values will distort the chart.

Insert a Scatter chart

Click Insert → Chart. In the Chart Editor, change the Chart type to Scatter chart. Sheets may auto-suggest a Line chart — always switch to Scatter for mathematical data so individual data points are shown correctly.

Add the trendline and display the equation

Switch to the Customize tab → expand Series → scroll down and tick Trendline. Change the Label dropdown to "Use equation". This outputs the exact regression formula (e.g. y = 2.34x + 5.17) directly onto the chart.

Show R²

In the same Series panel, tick "Show R²". The coefficient of determination appears on the chart automatically. Copy this value into your IA write-up and interpret it — do not just reproduce the chart without discussion.

Label axes and export

Under Chart & axis titles, add a descriptive chart title and both axis labels with units. Export via the three-dot menu → Download → PNG. Paste into your document with a figure caption below.

2 Calculating Pearson's Correlation Coefficient (r)

To mathematically justify the linear strength without relying solely on a visual chart, calculate r directly in a calculations cell:

Pearson's r

Correlation coefficient (r)=CORREL(A2:A101, B2:B101)

Coefficient of determination (R²)=RSQ(B2:B101, A2:A101)

How to interpret r in your IA. The value of r ranges from −1 to +1. In your write-up, state both the value and its meaning: "r = 0.91 indicates a strong positive linear correlation between temperature and sales." Then quote R² = 0.83 and interpret it as a percentage: "83% of the variation in sales is explained by temperature." Reference published critical values for your sample size n to test statistical significance — simply having r close to ±1 is not sufficient without this check.

Part 3 Advanced Regression Modelling

If your data displays non-linear properties, adjust the trendline model type in the Chart Editor. The table below summarises all available models and when to use each.

Model	Equation form	Trendline setting	Typical IA application
Linear	y = mx + c	Type → Linear	Any direct proportional or steady-rate relationship
Exponential	y = ab^x	Type → Exponential	Population growth, viral spread, compound interest, radioactive decay
Quadratic	y = ax² + bx + c	Type → Polynomial, Degree → 2	Projectile trajectories, optimisation problems, parabolic arches
Cubic	y = ax³ + bx² + cx + d	Type → Polynomial, Degree → 3	S-shaped growth, roller coaster profiles, cubic spline modelling
Power	y = ax^b	Type → Power series	Kepler's laws, braking distance, allometric biology
Logarithmic	y = a·ln(x) + b	Type → Logarithmic	Diminishing returns, Richter/decibel scales, learning curves

Validation tip — compare R² across models. Fit two or three different trendline types to the same data and record each R² value. Whichever model has an R² closest to 1.000 represents the strongest mathematical fit. In your IA, present this comparison as a table and justify your chosen model. Simply picking a model without testing alternatives will limit your score on Criterion D (Reflection).

1 Extracting Exact Regression Coefficients

The trendline equation shown on a chart is rounded. For your IA write-up, extract the full-precision coefficients using formulas.

Linear model (y = mx + c)

Gradient m=SLOPE(B2:B101, A2:A101)

y-intercept c=INTERCEPT(B2:B101, A2:A101)

Full regression output — LINEST (array formula)

Select a 2×2 empty block, then type:=LINEST(B2:B101, A2:A101, TRUE, TRUE)

Confirm withCtrl + Shift + Enter

Output: row 1slope (col 1), intercept (col 2)

Output: row 2R² (col 1), standard error (col 2)

Exponential model — linearise first (y = ae^bx)

Column C: take ln of y=LN(B2) — fill down to C101

Coefficient b (exponent)=SLOPE(C2:C101, A2:A101)

Coefficient a (initial value)=EXP(INTERCEPT(C2:C101, A2:A101))

Quadratic model — add x² column (y = ax² + bx + c)

Column C: x²=A2^2 — fill down to C101

Run LINEST over both predictors:=LINEST(B2:B101, A2:C101, TRUE, TRUE)

Confirm with Ctrl+Shift+Enter.Row 1 gives: a (col 1), b (col 2), c (col 3)

2 Generating Predicted Values and Residuals

Once you have your model coefficients in named cells, create a predicted values column alongside your raw data. This lets you plot the fitted curve and calculate residuals.

Example layout — linear model, m in E1, c in E2

AB (observed y)C (predicted ŷ)D (residual)

x valuesraw data=$E$1*A2+$E$2=B2-C2

↓ fill down↓ fill down↓ fill down↓ fill down

Plot columns A and C together (alongside A and B) on the same scatter chart to show how well your model fits the data. A residuals plot (A vs D) should show no clear pattern — if it does, your model is inappropriate.

Part 4 Statistical Hypothesis Tests

Before running any test, always write H₀ and H₁ explicitly in your IA. State your significance level (α = 0.05 is standard). After obtaining the p-value, write a formal conclusion.

1 Chi-Squared Test of Independence

Use when testing whether two categorical variables are independent (e.g. age group vs. preferred platform).

Build your observed frequency contingency table

Enter row categories in column A, column categories in row 1. Fill in observed counts. Add row and column totals using =SUM().

Calculate expected frequencies

For each cell: = (row total × column total) / grand total. Use absolute cell references for totals so you can fill the formula across all cells, e.g. =($G2*B$7)/$G$7.

Get the p-value

=CHITEST(observed_range, expected_range) — returns the p-value directly. If p < 0.05, reject H₀ and conclude the variables are not independent.

Check expected frequencies. The chi-squared test is only valid if all expected frequencies are ≥ 5. If some cells fall below this threshold, combine adjacent categories or acknowledge this as a limitation in your IA.

2 t-Test for Comparing Means

t-test formulas

Two-sample, two-tailed=T.TEST(A2:A30, B2:B30, 2, 2)

One-sample (vs known mean μ)Calculate t manually: =(AVERAGE(A2:A101)-μ)/(STDEV.S(A2:A101)/SQRT(COUNT(A2:A101)))

Then get p-value=T.DIST.2T(ABS(t_cell), COUNT(A2:A101)-1)

3 Normal Distribution Calculations

Normal distribution formulas

Cumulative P(X ≤ x)=NORM.DIST(x, mean, stdev, TRUE)

P(a ≤ X ≤ b)=NORM.DIST(b,mean,stdev,TRUE) - NORM.DIST(a,mean,stdev,TRUE)

Inverse — find x for given probability=NORM.INV(probability, mean, stdev)

z-score for each data point=(A2-AVERAGE(A$2:A$101))/STDEV.S(A$2:A$101) — fill down

Checking normality. Calculate a z-score column as above, then create a histogram of z-scores. If the distribution is approximately normal, it should be bell-shaped and centred near zero. In your IA, describe what you observe: "The z-score histogram is approximately symmetric and bell-shaped, consistent with the assumption of normality."

4 Poisson & Binomial Distributions

Discrete distributions

Poisson P(X = k), rate λ=POISSON.DIST(k, λ, FALSE)

Poisson P(X ≤ k)=POISSON.DIST(k, λ, TRUE)

Binomial P(X = k), n trials, prob p=BINOM.DIST(k, n, p, FALSE)

Binomial P(X ≤ k)=BINOM.DIST(k, n, p, TRUE)

Part 5 Presentation Standards for Criterion B

Mathematical communication (Criterion B, 4 marks) is directly affected by how you present Sheets output in your IA document.

Show your formulas

Don't just paste a number. Write the formula alongside the result: "Using =CORREL(A2:A101, B2:B101) yields r = 0.87." This proves you used mathematics, not guesswork.

Consistent decimal places

Decide on a precision and stick to it. Use 4 d.p. for r and R². Use Format → Number → Custom to enforce this in Sheets before screenshotting tables.

Label every table

Every table needs a title above it (Table 1: Summary statistics) and column headers with units. Use Format → Alternating colors for clean Sheets tables before exporting.

Clean chart styling

White background, no 3D effects, meaningful axis labels with units, a descriptive title. Avoid pie charts — they convey less precision than a bar chart and look unprofessional in a Maths IA.

Figure captions

Every graph in your IA needs a caption below it: Figure 2: Scatter plot of stopping distance vs. speed with quadratic regression curve (R² = 0.97). Reference the figure number in your prose.

Share your spreadsheet

Go to File → Share → Anyone with the link → Viewer and include the URL in your bibliography. This lets the examiner verify your raw data and formulas directly.

Ready to start your IA?

Use the other tools in the guide to pick a topic, understand the criteria, and avoid common mistakes.

💡 Topic ideas 📊 Criteria guide ✅ Checklist