Groundwater Modeling Using EXCEL:
Introduction: Simply put, groundwater will flow from areas of high to low water level. A basic understanding of differential calculus is necessary to derive the important equations and formulas that map the flow, but we will use an approximate algebraic approach to model the flow.
To model the system, it needs to be divided into small grid cells, which is easy in EXCEL. To analyze groundwater flow, the nature of boundary cells around the system must be specified. Boundary cells can either be "no-flow," across which groundwater is not allowed to flow, or a "constant head," where the water level is always fixed and groundwater can freely flow either in or out.
Example Analysis: In this example analysis, we modify a model written by Dr. Rex Hodges of Clemson University. Figure 1 shows the layout of the aquifer. To the north lies a lake at 100. On the west side a river drains the lake and flows south and joins a river from the east at an elevation of 65. The south river has an elevation of 100 at the southeast corner of the aquifer. We want to use Excel to find the distribution of water levels in the aquifer.
Figure 1.
Set Up the Iteration: Under Formulas and Calculation Options, turn off Automatic and make it Manual. Click the Office button in the upper left corner, select Excel Options, Formulas. Enable iterative calculation and change the maximum iterations to “1”. In cell A1 write "TWO-DIMENSIONAL STEADY-STATE MODEL", and in cell A2 enter your name and the date. We will construct an 8x8 cell model.
Creating the Boundary Cells: The
top, bottom, and left side will be constant head boundaries, and the right edge
will be a no-flow boundary as shown in Fig. 1. First, enter the values of the
boundaries in the cells as shown below:
|
West
Side |
North
Side |
South
Side |
|||
|
Cell |
Value |
Cell |
Value |
Cell |
Value |
|
B11 |
100 |
C10 |
100 |
C19 |
65 |
|
B12 |
95 |
D10 |
100 |
D19 |
70 |
|
B13 |
90 |
E10 |
100 |
E19 |
75 |
|
B14 |
85 |
F10 |
100 |
F19 |
80 |
|
B15 |
80 |
G10 |
100 |
G19 |
85 |
|
B16 |
75 |
H10 |
100 |
H19 |
90 |
|
B17 |
70 |
I10 |
100 |
I19 |
95 |
|
B18 |
65 |
J10 |
100 |
J19 |
100 |
For the no-flow boundary on the right side of the model, the head at the boundary is set equal to the head in the adjacent cell of the model. This forces no slope on the water level and, hence, no flow can occur across the boundary. Enter the following formulas into the cells on the boundary. The "K" cells do not really exist in the physical model, but are there to stop the flow at the east boundary of the model, which lies on the east side of the J cells.
in Cell use Formula
K11 =J11
K12 =J12
K13 =J13
K14 =J14
K15 =J15
K16 =J16
K17 =J17
K18 =J18
Filling in the Model’s Interior Cells: Next, enter a finite-difference relationship for each of the model's interior cells. This situation is steady-state flow with no recharge, which can be done by solving the Laplace equation using finite-differences. Thus, for each cell, sum the head in the four adjacent cells, and then divide by 4. For example, the finite-difference equation for cell F13 would be =(F12+E13+F14+G13)/4; just the average of its nearest neighbors.
But first we need to insert an initial value in each of the interior cells as a starting point for the iteration. This can be done with a shortcut in the cell’s formula. We will put an initial value in a cell outside the model grid, and then with Excel's logical operator "IF" enter this value into each interior cell. The complete formula for cell F13 becomes =IF($A$3= "ic",$A$4,(F12 +E13 +F14+G13)/4), meaning that if "ic" is entered in cell A3, then whatever value is found in cell A4 is placed in cell F13. If "ic" is not found in cell A3, then the finite-difference equation is used to calculate a value for cell F13.
First, format the interior cells and the no-flow boundary. Move the cell pointer to cell K18, press the left mouse button, and hold it down. While holding down the left mouse button, move the cell pointer to cell C11 and release the left mouse button. The interior cells of the model and the no-flow boundary should be shaded. Now right click, select Format Cells, Number, and change the number of decimal digits to two. Then click OK. Next type "ic" into cell A3 and a "4" into cell A4. The following equations must be entered into the interior cells of the model, but read on before you enter them!
in Cell use Formula
C11
=IF($A$3="ic",$A$4,(C10 + B1l +C12+ D11)/4)
C12
=IF($A$3="ic",$A$4,(C11 +B12+C13 +D12)/4)
C13
=IF($A$3="ic",$A$4,(C12+B13+C14+D13)/4)
C14 =IF($A$3="ic",$A$4,(C13
+ B14+C15+D14)/4)
C15
=IF($A$3="ic",$A$4,(C14+ B15+C16+D15) /4)
C16
=IF($A$3="ic",$A$4,(C15+B16+C17+D16)/4)
C17
=IF($A$3="ic",$A$4,(Cl6+B17+C18+D17)/4)
C18
=IF($A$3="ic",$A$4,(C17+B18 +C19 +D18)/4)
D11 =IF($A$3="ic",$A$4,(D10+C1l
+D12 +E11)/4)
D12
=IF($A$3="ic",$A$4,(D11 +C12+ D13 + E12) /4)
D13
=IF($A$3="ic",$A$4,(D12+C13+ D14+E13)/4)
D14
=IF($A$3="ic",$A$4,(D13 +C14 +Dl5 +E14)/4)
D15
=IF($A$3="ic",$A$4,(D14+C15 +D16+E15)/4)
D16
=IF($A$3="ic",$A$4,(D15+C16+D17+E16)/4)
D17
=IF($A$3="ic",$A$4,(D16+C17+D18+E17)/4)
D18
=IF($A$3="ic",$A$4,(D17+C18+D19+E18)/4)
E11
=IF($A$3="ic",$A$4,(E10+ D11+E12+F11) /4)
E12 =IF($A$3=
"ic",$A$4,(E11 +D12+E13+F12)/4)
..... etc.
Continue in the above pattern until all interior cells are filled in, which includes column J11 through J18. You can GREATLY simplify this process by using the copy and paste. Type the correct formula for cell C11 and press Enter. Then move the cell pointer to cell C11, right click, and "Copy". Next, move the cell pointer back to cell C12, press and hold the left mouse button and drag the cell pointer to cell J18. Release the left mouse button and you will have highlighted all the interior cells. Now right click, "Paste", and the correct equations should be entered throughout. Once you have copied from C11 to the remaining cells, check a few of the cells to see if they are correct. Excel should change the formula in each cell to reflect its relative position.
Calculating the Water Levels: Press the F9 key and the “4” should appear in each of the interior cells and the no-flow right-side boundary. At this point, save your spreadsheet. Now go to cell A3 delete the “ic”. The conditional IF statement now activates the finite-difference equation. Do one iteration by hitting the F9 key. Notice that a new value for the hydraulic head in each interior cell and the right-side no-flow boundary has been calculated. If you press the F9 key again, Excel will perform a second iteration, and the interior cell values will again change. Now keep pressing the F9 key until there are no further changes in the interior cells. You are manually going though the iteration. (Think about what a royal pain this would be if you had to do it with a calculator!) Notice that the interior cells of the model now reflect the general shape set by the boundary conditions --- groundwater flows from the lake on the north toward the southwest corner.
Automating the Iterations: Click on the “Office” button in the upper left and then Excel Options, Calculation Options, and change the maximum number of iterations to 100. Click OK on the bottom of the menu. Reset the initial conditions by putting “ic” back into cell A3. The interior nodes should reset to 4 after you hit the F9 key. Now remove “ic” from A3 and press F9. The model should iterate automatically.
Sink of Source of Water: The model can be used to see how an well or a recharge basin affects the water levels. For example, move the cell pointer to interior cell Gl5. Type in "30" and press Enter. You have now replaced the finite-difference formula with a constant head of 30, which represents the water drawdown level in a pumping well at that location. Reset the initial conditions in the model. Notice that cell G15 is still set at 30, since it is now a constant head cell. Run the model with maximum iterations of 100. The shape of the water surface in the interior now reflects the drawdown around the well. You can determine the effect of a recharge basin on the water level surface by putting a constant head of 92 in cell G15 and rerunning the model.
Plotting the Results: Us the following steps to create a three-dimensional surface chart of the water level surface head:
1. Select the block of cells you want to map (for this part of the groundwater flow assignment it would be C11 : J18)
2. Click on INSERT, Other Charts, Surface (use the 3rd selection with the colored interval). Note that (1) the plot slopes NW, not southwest like it should, and (2) the contour interval is too large (20 units) and should start at, say, 65 (not zero).
3. Change the vertical axis by going to Chart Tools, Layout, Axes, Primary Vertical Axis, More Options, Axes Options. Set the Minimum = fixed and 65, Maximum = fixed and 100, Major Unit = fixed and 5, Minor Unit = fixed and 1. Apply the changes.
4. Finally, change the depth axis by reversing the order. Choose Depth Axis, More Options, Axis Options. Put a check in the Series in Reverse Order box.
5. You can change the color, lines, shading, line patterns, etc. under the Chart Tools Format Selection menu item.
Figure 2.
Homework Problem: Now try analyzing a more complex 11x11-cell groundwater flow system (see map below). A constant head stream flows on the west edge and varies evenly from 100 feet on the north to 98 on the south. Enter these heads on a new EXCEL sheet in cells C3 to C13. Using the "Series" command (under "Edit" and "Fill" in the main menu), use a step of -0.2 to fill these cells. A no-flow boundary surrounds the system everywhere else, except for a constant head of 103 feet on the east, in cells N6 – N9. This represents a sewage lagoon. Constant heads can also be placed within the boundaries of the groundwater system; in this case, wetlands with a constant head of 102 feet occur in the area covered by cells H6, H9, H10, H11, I4, I5, I6, J6, J7, K6, and K7. Set the format to two decimal places. Figure out what formula you need in a cell to show an iteration counter. Put this outside the model cell block.
Run the steady state model. Map the results. Does contaminated groundwater
reach the river? (Contaminated groundwater cannot reach the river if there is a
north-south line of cells that have elevations greater than the water
level in the lagoon). Experiment with the model to determine what water level
in the wetland (to the nearest 0.01-foot) would be necessary to prevent flow of
sewage lagoon seepage to the river. This might be a practical method to
mitigate contamination of the river, provided there was a source of water to
pump into the wetland.
Please turn in a copy of your
11x11 cell worksheet and a chart of the surface.
