Solving Partial Differential Equations with Excel

To begin with, this post is not a part of the "Math, the Way it Should Be" series. Although it is a post filled with mathy-goodness, the mathematics involved here are far and away more complex than what I've presented so far in that series.  If you're inclined to think things are nerdy, you'll probably think this is pretty nerdy. That being said, let's begin with a short introduction and then I'll tell you what I did.

For complicated differential equations, we have three options for finding a solution.  In some cases, the solution may be found analytically but under some circumstances this may involve infinite series to match boundary and/or initial values.  The second option is to look up a solution that someone else may have come up with.  This approach has limitations as well as not every problem has already been solved by someone else.  This leads us to our third option, a numerical solution.  The limitations here are that there is always error (analytical solutions are usually exact), serious computing power can be required (I like to use MATLAB), and the last problem is that the user really has to know what he or she is doing in order to avoid unphysical or unrealistic solutions.  If you can get past these, however, you are on your way to a powerful tool.  Using the following steps, I used Microsoft Excel 2007 to solve the following problem (Laplace's Equation with mixed boundary conditions):

Note: y ranges between 0 and 5 and x ranges between 0 and 10

The first step is to set up Excel: The first step is to allow Excel to iterate on cells with circular references.  This is done by first clicking on the round Microsoft Office button in the upper left-hand corner and selecting "Excel Options".  Click on the "Formulas" tab.  You will see this:

Click the box enabling reiteration.  Set the maximum iterations to 32767 (this is the maximum number) and reduce the maximum change to the desired level of accuracy.  I like 0.0000001 for this application.

The next step is to prepare your domain.  This is done in the workbook itself.  I will be using 0.5 unit change in both x and y, so the boundary conditions are fairly easy to set up:

Now comes the mathy-goodness.  The next step is to discretize the equation.  This is where the math comes in and is a little bit more complicated than I can do a good job explaining.  The required background information can be found on Wikipedia.  Suffice it to say that from this, we find that the value at each point inside our domain is approximately equal to the average of the points in each cardinal direction.  Put mathematically, we have:

Note: i index indicates position on x-axis, j index indicates position on y-axis

This can now be entered into the domain.  Start in the upper left corner.  The proper entry (in this case) should be "=0.25*(E2+E4+D3+F3)".  Then take the black box in the lower corner of the selected cell and drag this equation across the domain.  Then do this again with the whole row, only drag downwards to cover the entire domain.  Watch as Excel iterates through and solves the equation over the domain!

The final step is to make it a little easier to see what's happening.  I started by adding color using the conditional formatting color scale option:

This is pretty cool and gives a pretty good idea of what's going on.  Another option is using the 3-dimensional graph:

If you've been following this, you're probably thinking this is pretty good.  I am always surprised by the things you can get Excel to do if you want.  If you haven't been following this but have still made it to the bottom of the post, I'm glad you stuck with it to the end but I apologize for an ending that's probably pretty disappointing.  The posts after this one deserve some less intellectual content and I intend to deliver.

-Lane