Modeling surfaces in Excel

It can be helpful in understanding surface modeling to create some surfaces and see what they look like. Excel provides some limited capacity to do this.

The following steps can be a guideline to how to produce such surfaces.

• Input your x values into a row with the first cell empty.
• Starting beneath the empty cell input your y values.
• Input the equation for z as a function of y and x in the cell beneath the first row entry (first x value). Make sure you use dollar signs so that for x values the row designation won't change, and for the y values the column won't change (e.g. \$B2 and A\$1). In this way the formula will refer to the appropriate x and y value for the cell at their column-row intersection.
• Copy the formula into the other cells. Go across the top rows first and then pull down through the columns. The appropriate z values are now in the various cells.
• Highlight all the z values and create a surface plot.
• Have fun exploring possibilities.

Examples:

The above is a model of simple plane. The equation is simply that z = a*x + b* y + d*RAND(). a and b are constants that determine the orientation of the plane and d is a constant that determines the size of the random fluctuations from the plane. Excel provides both a colored contour depiction on top of a wire mesh model. This is a simple surface to understand.

This is a model of a more complex surface that is semi-realistic, and could be imagined as some alpine topography. The equation that generated it was z = 8*SIN(.4*x) + 2* SIN(.45*y) + .4*y + RND()*2. The trig functions in Excel take Radians as units. Here the trig functions play the role of producing two undulations at right angles to each other. The numbers 8 and 2 determine the amplitude of the undulations, while the . 4 and .45 determine the wavelength. Change these numbers and you will change those aspects of the surface appearance. The third term give a overall regional slope or gradient in the y direction, and the last term adds the random fluctuation.

Of course the imaginary surfaces you can create this way are infinite. By playing with creating some of these you can develop your intuition for how surfaces can be expressed mathematically. This can help one in choosing which best-fit approach to take. While large complex surfaces are very difficult to impossible to model well with equations you can often choose a more limited area that is more amenable to this type of approach.