## as a Curve Fitting Tool

### Michael McCallum

I will attempt to show how this graphing capability and the other capabilities of MicroSoft Excel© can be used as a curve fitting tool.  There are addins available for Excel© that will do non-linear regression.  What I will show here is that you can do non-linear regression, though maybe not as accurately, without these addins.   All you need to know is what types of mathematical models fit the shape of a particular curve.

#### Cooling Models.

Elementary cooling can be modeled by the function where Ta is the ambient temperature, To is the temperature at time zero and k is a constant.  If the ambient temperature and the temperature at time zero are known, it is a very easy process to calculate k and write the equation.  What if we have a set of cooling data and the person who created the data forgot to record the ambient temperature?  Then we must use more of a trial and error method to find the cooling function.

The data set shown below is the cooling data from a cooling experiment done with water.   The water was heated to boiling and then allowed to cool.  The temperature of the water was recorded every minute for 30 minutes.  No ambient temperature was given.  We will find the cooling function and, in the process, estimate the ambient temperature.

```Time	  Temperature
0        	212
1  	      205
2	        201
3	        193
4	        189
5	        184
6        	181
7	        178
8	        172
9	        170
10	       167
11       	163
12       	161
13       	159
14	       155
15	       153
16       	152
17       	150
18	       149
19	       147
20	       145
21	       143
22	       141
23	       140
24	       139
25	       137
26	       135
27	       133
28       	132
29	       131
30       	130```

This data can be placed into a spreadsheet in Excel©.  Part of the spreadsheet is shown below with the data.

Let's make an initial guess at a cooling function.  Assume an ambient temperature of 75 degrees F and calculate the corresponding value of k at t = 30 minutes.  (see below)

Let's use this value for k and write an initial guess at the cooling function.

Using this guess, let's create another column in the spreadsheet and compare the values from the function with the empirical values in the spreadsheet.

I also added a column for residuals; that is, the differences between our function estimates and the empirical data.  Let's look at the last few rows of the spreadsheet and see what the sum of the residuals is.  We will use this value as a goodness of fit test as we make adjustments to the parameters in the function

.

As our function gets to be a better model for the cooling curve, sum of the residuals should decrease.  We can also graph this data to get a visual idea of how well our function fits the data.

Not a very good fit, is it?  What is wrong?   First, from the shape of the blue diamonds representing the empirical data it would seem that the ambient temperature is somewhat greater than 75 degrees.  We can say this because the graph is much less steep between 25 and 30 minutes, indicating that an asymptote was being approached.  Let's try an ambient temperature of 100 degrees F and see what parameters we get and what the effect is on the residuals.  With an assumed ambient of 100 degrees, k = -0.04391.  Using this value for k we replace the function and look at the result on the residuals and the graph.

Much better.  The sum of the residuals has decreased to -91.176 and the graph of the function looks much closer to the empirical data.   However, the size of the sum of the residuals indicates that we can still do better.  Lets try 110 degrees F as an estimate of the ambient temperature and see what happens.  At 110 degrees k = -0.054308.  Using these values in the function we will again look at the residuals and the graph.

Another dramatic decrease in the sum of the residuals to -34.28, and the graph appears to be almost perfect.  Using the sum of the residuals as a control we can continue incremental increases in the estimate of the ambient temperature.  At some point, the sum of the residuals will no longer decrease.   Then we must back up and use smaller increments.  Using this method, I found that the probable ambient temperature for this experiment was 114.2 degrees F.  This seems a little high, but if the container for the water was left on something like a stovetop, this is not unreasonable.  Using this value for the ambient temperature, k = -0.06076, and we get the following:

The sum of the residuals is now less than 5 one hundredths and the graphs of the function and the empirical data seem to lay one on top of the other.   This manual technique is similar to the more rigorous statistical techniques in that the objective is to minimize the sum of the residuals.  In the absence of sophisticated statistical software, such as SPSS or MiniTab, this technique can be useful.   There are four more spreadsheets available for different types of data where I have used this technique to do curve fitting.  If you wish to view the spreadsheets, click on one of the selections below.