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.

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.


Copyright by Harmon D. Maher Jr.. This material may be used for non-profit educational purposes if proper attribution is given. Otherwise please contact Harmon D. Maher Jr.