H2 Physics Spreadsheet Linearisation: ln Graphs, Trendlines and Time Constants

This guide extends the broad H2 Physics Paper 4 spreadsheet skills guide with the specific transformations students tend to miss under exam timing.

Status: SEAB H2 Physics 9478 requires spreadsheet use for practical assessment from the 2026 examination and provides an Excel reference guide for Paper 4.

1 | Linearisation choices

Choose the transformation from the equation, not from the graph shape by eye.

Always keep raw data columns unchanged. Add processed columns beside them so a marker can follow the workflow.

2 | RC discharge and time constants

For a capacitor discharging through a resistor:

$$I = I_0 e^{-t/\tau}$$

Take natural logarithms:

$$\ln I = \ln I_0 - \frac{t}{\tau}$$

Spreadsheet workflow:

1. Enter Time / s and Current / A.
2. Add a new column LN(Current).
3. Use =LN(B2) if current is in column B.
4. Plot LN(Current) against Time / s as an XY scatter graph.
5. Add a linear trendline and display the equation.
6. Calculate $\tau = -1/\text{gradient}$.

If the trendline shows y = -0.42x - 3.10, then:

$$\tau = -\frac{1}{-0.42} = 2.38 \text{ s}$$

The intercept is $\ln I_0$. To recover $I_0$, use =EXP(intercept).

3 | Power laws and reciprocal rearrangements

For a power law:

$$y = ax^n$$

Taking base-10 logarithms gives:

$$\lg y = \lg a + n\lg x$$

The gradient of $\lg y$ against $\lg x$ is $n$. The intercept is $\lg a$, so $a = 10^{\text{intercept}}$.

For reciprocal relationships, do not force a curved graph to become straight by choosing a narrow range. Create a processed 1/x column and plot the dependent variable against it. This is common when a variable is inversely proportional to distance, resistance, or another measured quantity.

4 | Trendline equation or LINEST

The displayed trendline equation is quick, but LINEST is better when uncertainty matters.

Use:

=LINEST(known_y_values, known_x_values, TRUE, TRUE)

Use LINEST when:

• the question asks for the standard error in the gradient;
• you need more significant figures than the chart label displays;
• you need to show a reproducible calculation route;
• multiple processed columns make the chart equation hard to audit.

Use a chart trendline when the question only asks for a best-fit line and the gradient can be read from the displayed equation.

5 | Radians in spreadsheet trigonometry

Excel and most spreadsheet tools use radians in SIN, COS, and TAN.

If the angle column is in degrees, convert before applying trigonometry:

If you use =SIN(30), the spreadsheet treats 30 as 30 radians, not 30 degrees. That error can make a later gradient look physically impossible.

6 | Area under curve and trapezoidal summation

Some Paper 4 tasks ask for the area under a graph, not just a gradient.

Use trapezoidal summation between consecutive data points:

$$\text{area strip} = \frac{1}{2}(y_1 + y_2)(x_2 - x_1)$$

Then sum all strips.

Spreadsheet column pattern:

1. Keep raw x and y columns.
2. Add Delta x as =A3-A2.
3. Add Mean y as =(B3+B2)/2.
4. Add Strip area as =C2*D2.
5. Use =SUM(E2:E20) for the total area.

Do not average all y-values and multiply by total time unless the data points are equally spaced and the question accepts a rough rectangle estimate. The trapezoidal method is the safer Paper 4 response.

7 | Common spreadsheet mistakes

• Plotting a line chart instead of an XY scatter graph.
• Putting transformed data on the wrong axis, so the gradient has the inverse meaning.
• Using LOG10() when the model calls for natural logarithm, or using LN() and then interpreting it as $\lg$.
• Forcing the trendline through the origin without a physics reason.
• Rounding transformed columns too early.
• Interpreting a negative RC gradient as a negative time constant.

8 | Related guides

• H2 Physics Paper 4 spreadsheet skills guide
• H2 Physics capacitor charge and discharge practical
• H2 Physics practical 2026 lab mastery guide

Sources

1. SEAB - H2 Physics (Syllabus 9478) GCE A-Level 2026
2. SEAB - H2 Physics Specimen Paper 4 (9478/04), first exam 2026
3. SEAB - 9478 Physics Excel Reference Guide 2026

Contact us to learn more about our tailored packages and available pricing options.