Spreadsheet Tutorial for Engineering Analysis and Documentation by Nathan Delson (ndelson@ucsd.edu)
Spreadsheet Advantages and Disadvantages Spreadsheets are powerful tools for engineering calculations, and easy to use. In fact it has been claimed that spreadsheets were one of the original "killer applications" that convinced businesses to invest in personal computers. Spreadsheets were originally developed for accounting applications, but now include many engineering, statistics, and database functions. Key advantages of spreadsheets for engineering applications are that they are relatively easy to setup and modify. It is possible to change one design variable, and quickly see the effect on all other variables in the design. However there is an important disadvantage of spreadsheets in terms of its ability to document design calculations. When one prints out a spreadsheet the default format only shows the numerical results, and not the equations that have been used. Even with the spreadsheet on-line, it can be difficult to read and verify long equations. Thus it is not easy to have someone verify and check the analysis (real world engineering errors have occurred because of errors in a single spreadsheet cell). In this tutorial a method is presented to name variables, and printout both the numerical values and the equations in an easy to read fashion. This approach helps offset this disadvantaged of spreadsheets (an alternative software tool for engineering documentation is MathCAD).
Excel Basics (other spreadsheet programs are similar) Each cell has a designation for row and column, such as A1 or B5 Cell types can be: text, numeric, or dates Any cell that is entered as text is automatically treated as a text type If numbers are entered, then the cell is treated as a numeric type. One can force a cell to be treated as a numeric type by starting it with: =, +, or – Dates are entered in most standard formats
Equations can be written by typing in cell locations. For example: If one types in a cell the following: =A3+B5 Then the numeric value of Cell A5 plus B5 will be displayed When writing an equation, one can simply move the cursor to the desired cell that is being used in the equation, rather than typing in the cell location
1
When copying equation, the values of the row and column increment automatically according to the location of the new equation. One can prevent the automatic update of rows and column by typing “$” in front of the row or column that should be fixed (the F4 key will also do this). For example: In a cell equation typed as: =$A$3+B5 only B5 will be incremented automatically when copied, and $A$3 will remain fixed.
Displaying Equations
To display equations type "ctrl ~". This will toggle back and forth between displaying equations and values. You can then printout or save the spreadsheet with the equations visible. The problem with just creating equations as shown above, is that it is difficult to read and check the equations, because it is hard to read all the row and column numbers. For example, look at the equations in the sample file without variable names (you can see the equations with “ctrl ~”, but it would be hard to certify that all equations are correct, especially for long equations). To solve this problem, follow the approach described below for named variables.
Displaying Equations with Named Variables
Use the method described below shows equations more clearly and allows someone to really verify a spreadsheet’s calculations. Create columns as shown below: First column has the variable description Second column has a unique variable name with no spaces Third column has values and equations Fourth column has units
Variable Description Area Thickness Volume Density Estimated Weight Actual Weight Percent Difference Variable Name A t Vol p W_est W_act Values Units 15.40 in^2 0.34 in 5.24 in^3 0.69 oz/in^3 3.59 oz oz
2
Defining Cell Names
Select region that includes columns of variable names and values, as shown below.
Select Commands: Insert => Name => Create => Left Column => OK
When equations are written, the variable names will be automatically shown instead of the cell number (see variable name example). If variable names are added or changed, the above step needs to be repeated. If one names the cells after the equations are written, then names need to be applied by executing: Insert => Name => Apply => (select all names) => OK
Displaying Named Equations
To display equations type "ctrl ~". This will toggle back and forth between displaying equations and values. You can then printout or save the spreadsheet with the equations visible.
3
When submitting a report one can printout two copies of the spreadsheet; one with the values shown and the other with the equations shown. You can save web pages in both formats as well.
Naming Columns
Many equations in worksheets are copied down a column. To use named variables in columns, highlight one column at a time including the name in the top row. Execute the command: Insert => Name => Define => Add. As shown below the name “Price” is being defined for cells in column B.
Now each cell in the column will be named according to the text in top row of the column. The cells will maintain their dependence on the row of the spreadsheet, but will display the proper equations for the column, as shown below:
4
Wrap Text and Paragraphs A long sentence or paragraph will extend on a single line, making it difficult to read. The text can be formatted for easier readying using the "Wrap text" and "Merge cell" options.
Step 1: Select range of cells that includes the cell with text and empty cells to the right and below this cell, where you want text to wrap into Step 2: Use command Format => Cells => select tab Alignment => check "Wrap text" and "Merge cells"
5
Text Tips
Once you get a format of a paragraph you like, you can copy it to other cells to start new paragraphs. You can also apply the format from one cell onto another cell using the command: Edit => Paste Special => check "Formats" To start a new line of text within a cell at a specific point, click in the formula bar (or cell, if editing in the cell) where you want to break the line, and then press ALT+ENTER. To create additional rows in the middle of a spreadsheet, select a number of rows with the mouse, and use commands: Insert => Rows To delete rows, select the complete row by clicking the mouse on the row numbers on the far left side of the spreadsheet. Then use command: Edit => Delete (the same method works for columns) Spell checking is easy, just us commands: Tools => Spelling
Additional Tips
Formatting Cells
The Format => Cell command provides many options and can be impended on a range of cells at once o Changing the number of significant digits: selecting the “Number” tab and “Number” on the list allows one to set the number of decimal places
6
o Percentages: selecting the “Number” tab and “Percentage” on the list displays a fraction as a percent
Column Width
The column width can be changed by the commands: Format => Column => Width Alternatively one can adjust the column width with the mouse by dragging the column line on the top row If you see ###### in a cell, it means that the column is not wide enough to display the numeric contents; increase the column width.
Saving as a Web Page
An Excel spreadsheet can be saved as a web page. Just use: File => Save as Web Page A nice feature of Excel, is that it can open and edit a web page (htm file) created by Excel. Therefore you do not need to have tow versions of your Excel spreadsheet. Just have the spreadsheet as a web page, and open that page from within Excel to edit it. It can be confusing if you forget to save your file as a web page, but still link to it on your web page. A computer with Excel installed will start up Excel and load the file for editing rather than view it as a web page.
7
Paste Special
The Edit => Past Special is a very useful command for copying cells within a spreadsheet The following window will appear
o selecting values will copy only the numeric values displayed without the underlying equations o selecting format will copy the format of a cell into other cells, but without changing the values in those cells o selecting transpose will transpose a column to a row or vice versa
What-if Analysis
There are many advance analytical tools in spreadsheets, but “what-if analysis” deserves a special mention. Using this approach, one can see what happens when one changes the value of a single cell in a spreadsheet over a range. The results of any other cell in the spreadsheet can be tracked. See Excel help on “One-variable data tables” for an example and detailed instructions Other Excel Tutorials on the Internet Concise Guidelines to Common Operations, from Purdue http://www.chem.purdue.edu/gchelp/tools/operate.html Short Tutorial on Excel, from Mike Splane http://www.cob.sjsu.edu/splane_m/ExcelFormulas.htm A list of spreadsheet tutorials on the Internet, from Quasar Canada http://www.quasar.ualberta.ca/edpy202/tutorial/spreadsheet/spreadsheet. htm
8