Solving the 1D Heat Equation Using Finite Differences

Introduction
The Heat Equation describes how temperature changes through a heated or cooled medium over time and space.  In one dimension, the heat equation is

1D Heat Equation

This post explores how you can transform the 1D Heat Equation into a format you can implement in Excel using finite difference approximations, together with an example spreadsheet. If you just want the spreadsheet, click here, but please read the rest of this post so you understand how the spreadsheet is implemented.

We will model a long bar of length 1 at an initial uniform temperature of 100 C, with one end kept at 100 C.

 

Finite Difference Approximations
The central and forward difference approximations for the 1st derivative wrt time and the 2nd derivative wrt space are

Forward and central difference approximations

Substituting these relationships into the heat equation and rearranging gives an equation that describes the temperature u at position x along the bar and time t+Δt. 

Equation 1

Equation 1 is in a form that can be implemented in Excel.  Note that we only need to know the temperature of the bar at time t to know the state at time t+dt, as illustrated below.

Boundary Conditions
Now lets define the initial and boundary conditions. The left-hand side (i.e. x=0) of the bar is kept at a fixed temperature of 100 C , while the initial temperature is 0 C.

We also need a boundary condition on the right hand side of the bar at x=1.  The rate of change of temperature with respect to distance on right hand side of the bar is 0.

A central difference approximation to this boundary condition is

Rearranging this gives

Equation 2

Now, Equation 1 on the right-hand boundary (at x=1) can be rewritten

Equation 3

 Substituting Equation 2 into Equation 3 so that we eliminate u(1+Δx, t) we get

Equation 4

Equation 4 describes the boundary condition on the right-hand side of the bar in a form that can implemented in Excel

Excel Implementation
To summarize, now we have

• Equation 1 - the finite difference approximation to the Heat Equation
• Equation 4 - the finite difference approximation to the right-hand boundary condition
• The boundary condition on the left u(1,t) = 100 C
• The initial temperature of the bar u(x,0) = 0 C

This is all we need to solve the Heat Equation in Excel.  This screengrab represents how the system can be implemented, and is color coded according to the legend below.

Rows represents the distance along the bar, with time increasing as you go down.

If you want the Mathcad implementation, then click here.

Download Excel 97 spreadsheet for solving 1D Heat Equation

Solving the Colebrook-White Equation with Excel

The Colebrook-White describes the relationship between the Reynolds Number Re, the pipe relative roughness e, and the friction factor f in turbulent pipe flow.

Usually, we fix a value for Re and e, and compute f.  However, this equation cannot be solved explicitly, so numerical iteration is needed.

There's several ways we can do this in Excel.  I'll outline the two most convenient methods.  If you just want the spreadsheet, scroll to the bottom of this post and download the spreadsheet.

Spreadsheet Setup
This is a screengrab of the spreadsheet included with this post.

Note that the parameters to be entered by the user are highlighted green. I've also assigned cell names to each parameter, so the pipe roughness is e, the pipe diameter is D, and so on.

Method 1 - Using VBA and Goal Seek
This VBA macro fires up Goal Seek whenever there is any change in the worksheet (due to the Worksheet_Change() event).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bSuccess As Boolean
    On Error Resume Next
    bSuccess = Range("fCheck").GoalSeek(0, Range("f"))
    On Error GoTo 0
    If Not bSuccess Then
        MsgBox "Goal Seek Failed for Cell ""X""!"
    End If
End Sub

The code finds the value of f that will make fCheck equal 0.

Method 2 - Worksheet Iteration
You'll need to enable worksheet iteration so that Excel can correctly resolve a circular reference.  In Excel 2010 go to File>Options>Formulas, and check Enable Iterative Calculations.  You might also want to increase the number of iterations to 500, and reduce the maximum change to 0.0001).

The trick here is to rearrange the Colebrook-White equation so you have nothing but the friction factor f on the left hand side of the equation (it can appear on the right-hand side together with the other terms in the equation).

This is the formula I've typed into Cell B17 of the spreadsheet - it's just the rearragned Colebrook-White Equation, but with one minor change.

=1/(-2*LOG(e/(D*3.7) + 2.51/(Re*SQRT(B17+1E-300))))^2

When Excel starts iterating, it initializes B17 with a value of zero.  However, this will a divide-by-zero error.  To resolve this, I've added a very small number (1E-300) to B17.  This doesn't significantly change the accuracy of the computer friction factor.

Both Method 1 and 2 give roughly the same value of f.  I prefer Method 1 (purely because enabling spreadsheet iteration means you will not necessarily be informed of any unintended circular references).

Download Excel 97 Spreadsheet for Solving the Colebrook-White Equation

Download Excel 97 Spreadsheet to Solve Colebrook equation with Automated Goal Seek (VBA)