Data Manipulation - Regression

To complete the remaining categories in the table, the students will need to manipulate the data to graph the curve and also determine information concerning the curve.
 
The exponential form of Newton’s Law of Cooling, using integral calculus on equation (1) is
 
                                    (2)
 
Most logarithmic regression programs, Excel included, fit data to the form:
 
                                                    (3)
 
Since this is not Naperian base (base e), it is not the best fit for the data.  Therefore, equation (2) must be manipulated to find the best fit as follows:
 
                                    (4)

Taking the natural logarithm of both sides yields:

 

                        (5)

 

                        (6)

 

                        (7)

 

Since ln(C) is a constant, this equation is of the form:

 

                                                (8)

 

which is a linear equation.  Linear regression can now be used with time as the x-values and ln(T(t)-Tamb) as the y-values.

Below the data on the Excel spreadsheet, label two adjacent cells as m or slope and b or intercept.  Highlight the two cells underneath these labels.

Go to the top menu bar and select Insert then Function; under function select LINEST.  The following will appear:

 

 

Enter in the range of Known_y’s [ln(T(t)-Tamb)] and then the range of Known_x’s [time] and press OK.  Then click on the formula in the formula bar and then press Ctrl-Shift-Enter. The values for the slope and intercept will appear in the highlighted cells.

Now that the slope and intercept are found, the linear equation must be returned to its exponential form in order to graph the regression curve.  Rewrite equation (8) as follows:

 

                                    (9)

 

                                        (10)

 

                                         (11)

 

                                        (12)

 

In equation (12), eb is C from equation (2) and emx is ekt from equation (2).  The students can use equation (12) to form the data points in order to plot the regression curve in Excel.  In the Regression column of the spreadsheet the students should enter the Excel equivalent of equation (12) and then fill down.

 

 

This set of points can be graphed on the previous graph using the same steps as previous, except highlighting the cells under “Linear Regression”

 

 

Data Manipulation – Cooling Rate

The cooling rate is the slope of the cooling curve; therefore,
 
                                (13)
 
This equation can be input into Excel and fill the cells with the fill down function.
 

 
The “Derivative (T’)” column is the derivative of the regression equation found for the data.  This is
 
                                            (14)
 
The Cooling Rate and the Derivative (T’) can now be plotted on a graph.

 

 

 Back