Asee peer logo

Spreadsheet Techniques For Engineering Professors: The Case Of Excel And Engineering Economics

Download Paper |


2007 Annual Conference & Exposition


Honolulu, Hawaii

Publication Date

June 24, 2007

Start Date

June 24, 2007

End Date

June 27, 2007



Conference Session

Effective Tools for Teaching Engineering Economy

Tagged Division

Engineering Economy

Page Count


Page Numbers

12.1297.1 - 12.1297.13



Permanent URL

Download Count


Request a correction

Paper Authors


John Ristroph University of Louisiana-Lafayette

visit author page

JOHN H. RISTROPH is an emeritus Professor of Engineering Management. His doctorate is in industrial engineering and operations research, and his non-academic experience includes service to the Louisiana Department of Natural Resources first as Head of Economics and Statistics and then as Director of Policy and Planning. His interests include engineering economics and computer-aided-instruction.

visit author page

Download Paper |

NOTE: The first page of text has been automatically extracted and included below in lieu of an abstract

Spreadsheet Techniques for Engineering Professors: The Case of Excel and Engineering Economics


This paper provides engineering professors with techniques for using spreadsheets to improve teaching. It focuses on how to use the software, rather than classroom dynamics, by explaining methods for applying Excel to engineering economics that can be used in other disciplines. It first discusses intrinsic functions, and then it develops custom functions that use notation familiar to a student, such as PF(i , n) for (P|F, i , n). Next it covers how to produce diagrams and graphics via the drawing toolbar and custom cut-and-paste libraries, as well as how to show all formulas and logic rather than just numeric results. Then it describes hiding and protecting answers used to check student work, and it addresses common notational issues such as subscripts, super- scripts, and Greek symbols. Finally, it uses forms to enhance the foregoing procedures.


Students are encouraged to use spreadsheets via numerous examples in textbooks, but what about professors? Do they use spreadsheets to solve routine problems, such as those given for home- work, and in turn encourage students to do so? Probably not. One obstacle is the differences be- tween spreadsheet notation and that of textbook or manual solutions. Similarly, drawing and la- beling diagrams can be time consuming. Another major concern is that of security: Copying and pasting computer solutions is even easier than the time honored tradition of manual cheating.

This paper examines the foregoing issues by first exploring simple solutions and then progress- ing to more advanced methods. The presentation is based on Microsoft’s widely used Excel soft- ware [1], but other systems can be used as long as they support user-written routines known as macros to extend their capabilities. For example,’s free Calc [2] system has most of the capabilities of Excel, but it uses a different dialect of Basic for its macro language. EIOf- fice [3] is an integrated office system with a spreadsheet very similar to Excel that uses Java to program its macros. Most engineers are familiar with engineering economics, so examples are given for that field. The approach, however, is applicable to other engineering fields, and several references [4-13] allow extending the material presented below.

Factors and Functional Notation

Spreadsheets have a wealth of built-in or intrinsic functions. Excel’s financial functions are com- putationally similar to factors used in textbooks, but notational differences sometimes slow learning. The Visual Basic for Applications (VBA) component of Excel allows custom or user- written functions that correspond clearly to factors. For example, Table 1 shows how easily the challenging (P|A,g,i,n) factor is implemented as an electronic factor or Efactor.

Use the Tools|Macro|Security menu to allow macros by setting security at a medium or low level. Then click Tools|Macro|Visual Basic Editor (or key Alt + F11) and click the editor’s

Ristroph, J. (2007, June), Spreadsheet Techniques For Engineering Professors: The Case Of Excel And Engineering Economics Paper presented at 2007 Annual Conference & Exposition, Honolulu, Hawaii. 10.18260/1-2--2288

ASEE holds the copyright on this document. It may be read by the public free of charge. Authors may archive their work on personal websites or in institutional repositories with the following citation: © 2007 American Society for Engineering Education. Other scholars may excerpt or quote from these materials with the same citation. When excerpting or quoting from Conference Proceedings, authors should, in addition to noting the ASEE copyright, list all the original authors and their institutions and name the host city of the conference. - Last updated April 1, 2015