Spreadsheet Advantages and Disadvantagesby Nathan Delson (ndelson@ucsd.edu) and Daniel Y. 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). Listen to the inventor of spreadsheet explain how he developed the first spreadsheet and used it to impress his business class of how quickly he could re-calculate numbers. See: NPR Podcast Part 1: Excel BasicsDownload Bill of Materials (BOM) Example Here: Open the Following Table in Excel: Each cell has a designation for row and column, such as A1 or B5 Cell types can be: text, numeric, or dates:
Fill out the "Subtotal" Column: To obtain the subtotals, multiply the Price and the Quantity. Equations can be written by typing in cell locations using the equals sign. For example:
Some tips:
Toggle Equations and View Equations: To toggle and view equations, hit 'CTRL' and '~'. This will allow you to view all of your equations to check your work. Make sure you toggle back!You also double click each cell to view equations: Calculate Tax by Using $ to "fix" a Cell: Sometimes, we do not want Excel to automatically update the Rows and Columns when we copy and paste equations. We can "Fix" these cells using a dollar sign ($):
Tax can be calculated by: From our spreadsheet, we can calculate the subtotal with tax. When you select the 8% tax value, you must use dollar signs ($) to fix that cell in the equations! We can use Excel's built in functions to calculate different values:
When selecting cells, you can Shift+Click to select multiple adjacent cells, or CTRL+Click to select individual cells. Part 2: Excel Point Mass Analysis: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. This makes it easy to make systematic errors. To help mitigate this issue, we can name variables making it easier to check our work. Download Timing Analysis Example Here: Naming Variables: Start by creating a column of variable names. Variable names must be:
Next, selected the variable and the associated cell values as show below: From Window Menu: Formulas tab > Create from Selection (should be near Name Manager) > Left Column > OK NOTE: the old keyboard shortcuts for "show equation" may not work - use "Show variables" button on the same toolbar Now the columns on the right will be associated with the variable name on the left. Writing Equations: We can now write our equations using these variables. For example
NOTE: the old keyboard shortcuts for "show equation" may not work - use "Show variables" button on the same toolbar You will use this excel template as well as create your spreadsheet to analyze your clock timing for point mass and rigid body assumptions, due in weeks three and four. Printing Reports: A report should include 2 print outs of each Excel sheet: 1 printout showing numerical results See additional Excel Tips: here |
Tutorials >