Excel Spreadsheet Basics for Engineers

Robert B. Wilcox, P.E.

Course Outline

This course will cover spreadsheet based analysis for general purpose engineering use. It will focus on using basic calculations, formulas and graphs within Microsoft Excel ™. Several sample problems will be modeled, accompanied by sample spreadsheets which may be downloaded and used for understanding the examples. One powerful feature of the spreadsheet lies in its capability to rapidly compare the effects of changing parameters on the solution to an engineering problem. In this way, the sensitivity of the solution to each variable may be determined. Design examples will be given in the areas of stress analysis, heat transfer and engineering economics. Additional topics will include decision making logic, and goal-seeking (a form of automated trial and error). Course Requires Microsoft Excel™ or similar program, (such as Open Office Calc - an open source free program available as part of the Open Office Suite, see http://www.openoffice.com/)

• Prerequisites and Required Software
• The Basic Elements of a Spreadsheet
• Entering Data
• Basic Operators
• Entering a Formula
• Goal Seeking
• Introduction to Math Functions
• Creating a Chart Embedded in the Worksheet
• Statistical Functions
• Financial Functions
• Logical Expressions
• A Data Table Example
• Sorting Data
• Filtering Data
• Importing Data from the Web into a Spreadsheet
• Formatting Basics
  

The course includes a multiple-choice quiz at the end, which is designed to enhance the understanding of the course materials.

Learning Objective

At the conclusion of this course, the student will:

• Understand the basic elements of a spreadsheet;
• Comprehend absolute and relative references;
• Know the difference between a workbook and a worksheet;
• Be able to enter a simple formula;
• Understand basic operators and the order of operations;
• Learn what a function is, and the syntax of a function;
• Learn to apply basic math functions like sin, cosine, and square root;
• Be able to apply basic logical operations;
• Learn to use basic statistical functions;
• Comprehend the use of basic financial functions;
• Be able to construct a logical expression using operators like IF, AND and OR;
• Learn what goal seeking is and how to use it to solve for the optimal answer to a problem;
• Learn how to set up a data table;
• Understand how to sort data;
• Comprehend basic filtering of data;
• Learn how to import data from a web-based source into a spread sheet;
• Understand basic chart functionality;
• Know how to format cells;
• Learn about formatting charts;
• Apply the basic concepts to a parametric study of sizing a beam;
• Be able to set up a problem with multiple variables and solve for the best solution;
• Understand how to solve an engineering economics problem involving internal rate of return;
• Be able to apply the goalseek function; and
• See how embedded graphs can be used as an immediate feedback tool in the analysis.
  
  

Intended Audience

This course is intended for engineers or architects who want to learn how to use spreadsheets effectively, primarily for analysis of physical, real-world problems.

Benefit to Attendees

The attendee will obtain the basics required for setting up a spreadsheet based analysis, and be introduced to some features they might not ordinarily use, such as data filtering, logical tests, and goal seeking solutions.


Course Introduction

Engineers and architects frequently find themselves performing repetitive calculations on similar problems. Using spreadsheets as a working tool provides a means for a powerful semi-automatic parametric design approach. By using variables and recording the calculations in a spreadsheet, it is possible to rapidly and inexpensively evaluate several alternatives before finalizing the design. If a similar design situation then arises in the future, the problem is already framed and it becomes simply a mater of changing the values of the design variables to achieve a satisfactory new solution.

This course will cover the basics from square one, moving along quickly, progressing through to the design examples, and presenting some perhaps lesser known but very powerful features involving data sorting and filtering, logical decision making, and taking some of the mystery out of setting up the formulas in the spreadsheet based design.

Course Content

The course content is in a PDF file (500 KB) Excel Spreadsheet Basics for Engineers.

Please click on the above underlined hypertext to view, download or print the document for your study. Because of the large file size, we recommend that you first save the file to your computer by right clicking the mouse and choosing "Save Target As ...", and then open the file in Adobe Acrobat Reader. If you still experience any difficulty in downloading or opening this file, you may need to close some applications or reboot your computer to free up some memory.

Course Summary

Excel is a powerful analysis tool for the engineer or architect, with capabilities which go far beyond simply keeping track of data. Calculations may be standardized, optimized, reiterated, and compared rapidly and with graphical feedback. Most basic mathematical, financial and logical functions used in everyday calculations can easily be set up in a spreadsheet, saving time and providing a benchmark for future reference.

Related Links

For additional technical information related to this subject, please visit the following websites or web pages:

http://www.freebyte.com/cad/calculator.htm
http://www.usd.edu/trio/tut/excel/
http://office.microsoft.com/en-us/excel/HA010346321033.aspx
http://www.structural-engineering.fsnet.co.uk/

Quiz

Once you finish studying the above course content, you need to take a quiz to obtain the PDH credits.

---

DISCLAIMER: The materials contained in the online course are not intended as a representation or warranty on the part of PDH Center or any other person/organization named herein. The materials are for general information only. They are not a substitute for competent professional advice. Application of this information to a specific project should be reviewed by a registered architect and/or professional engineer/surveyor. Anyone making use of the information set forth herein does so at their own risk and assumes any and all resulting liability arising therefrom.

---