Solving Laplace equation in MS Excel for two dimensional solution

Laplace equation is second order derivative of the form shown below.

where phi is a potential function.

This equation is used to describe the behavior of electric, gravitational, and fluid potentials. In the study of heat conduction, the Laplace equation is the steady-state heat equation. This equation also describes seepage underneath the dam.

Direct solution of this equation is very tedious( and uninteresting). Numerical technique gives another approach to solve this equation. In numerical method, the solution space is converted in discrete number of points (a two dimensional array). Any point 'p' inside the solution matrix is the average of its surrounding points (4 points if 4 direction is considerer, 6 points if diagonal elements are included). The known points will be the potential(Phi value) at the boundary. Iteration has to done until desired accuracy is obtained. (similar to playing Minesweeper)

Excel sheet is nothing but a huge matrix.Thus providing ideal place to play with matrix. To solve Laplace equation, first of all each cell should be given a fixed width, i.e. say each cell will be 0.5m. After that, draw boundary, by putting the known phi value at the boundary. Any cells within the boundary will now given a formula which says =average(all surrounding cells). By doing so you will create cyclic function which will run automatically until desired level of accuracy is met. You must enable iterative calculation and set the desired accuracy in excel in its option menu.

I will demonstrate this with an example of seepage problem underneath the dam(green) as shown in figure. The dam has a pile in the upstream toe. There is seepage treatment 25m u/s and 30m d/s of dam. The base width of dam is 10m. The pile is 7m deep sheet pile (where seepage=0). The u/s water head is 5m and d/s water head is 1m. We are interested in distribution of water head inside the soil ( shown in yellow). As 2 dimensional Laplace equation will describe the flow field in this problem, we will solve it in excel sheet. Let us suppose at 25m below the dam foundation there is impermeable layer (another impermeable boundary).

Each cell is considered to be1.0m wide. Red cells denotes the boundary. The pile is also hydraulic boundary. The green is dam (done to show similarity with picture). Now a formula is written in any cell stating it as average of 4 surrounding cells (for e.g I10=AVERAGE(I9,H10,I11,J10)) and copy the cell in yellow zone. The boundary condition at u/s is phi=5m water head and at d/s is phi=1m of water head. After you copy formula to each of yellow cell, excel starts to calculate. For this iterative calculation has to be turned on (check Excel>options>formula). The higher the number of iteration, more accurate the result. Sometimes you may have to hit "Calculate sheet" button multiple times to get the result. A chart will show equipotential lines. A sample is shown in figure below. The excel sheet can be downloaded here.