How to find extrapolated temperature?

1 Answer

WARNING! This is a long answer.


Assume that you are doing a coffee cup calorimeter experiment in which you have to measure #ΔT#. You measure the temperatures over a period of time and plot a graph.

But the maximum temperature is not that shown on the graph, because the calorimeter is not a perfect insulator. It is constantly losing heat to the room.

The teacher asks you to use Microsoft Excel to extrapolate the data back to #"time" = 0#.

Here's how I do it using Excel 2010 for Windows. You may have to modify for your version.

First, watch Mr. Pauller's video on how to use Excel for linear regression.

Enter your data into Columns A and B of the Excel spreadsheet.

Assume that your data are:


Highlight the data from A1…B13. Click on Insert Charts.


Click the small triangle under Scatter. Select the first option (the graph with just points and no lines).

You should get a graph that looks like this.

Graph 1

The peak at (5.0, 30.0) has the maximum temperature. The points from 5 min to
12 min are in a sloped but linear region.

Go back to the spreadsheet and copy these values into column C directly across from their values in column B (as in the data table above).

Right-click in a blank portion of the Plot Area. Click on Select Data…

The Select Data Source window will open. Click the Add button.

The Edit Series window will open. Click in the Series X Values space and highlight cells A2…A13.

Delete the entry in the Series Y Values space and highlight cells C2…C13. Click OK, then OK again.

A new series of points appears on your graph.

Graph 2

Delete the entries in cells B7…B13, since they are now duplicated in C7… C13.

Right-click on a point in Series 2. Click on Add Trendline…

The Format Trendline window will open. Choose Linear Trend/Regression. Click Display Equation on Chart. Then Close.

If necessary, move the equation to a clear area on the chart.

Graoh 3

The trend line appears and extends back to intersect the #y#-axis at about 32.5 °C.

You can also set #x = 0# in the regression equation #y = 0.5 x + 32.5#. Again you get 32.5 °C for the extrapolated temperature.

Here is the source document for the original answer.