Spreadsheet Advantages and Disadvantages

by 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 Basics

Download Bill of Materials (BOM) Example Here:

BOM_Example.xlsx

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:

• • 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

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:

=B2*C2 will multiply the Qty with the Price

Some tips:

• • When writing an equation, you can click the desired cell that is being used in the equation rather than typing in the cell location.

  • You can copy and paste equations and Excel will automatically update the rows and columns using the pattern you specified.

  • You can highlight an equation and drag it to populate rows and columns below.

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 ($):

• • Putting a $ in front of the letter will fix the column $G3

  • putting a $ in front of the number will fix the row G$3

  • putting a $ in front of both letter and number will fix the column $G$3

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!

Using Functions to Calculate Totals and Averages:

We can use Excel's built in functions to calculate different values:

• =sum(....) adds up all of the cells

• =average(...) calculates the mean of the cells

• and many more...

When selecting cells, you can Shift+Click to select multiple adjacent cells, or CTRL+Click to select individual cells.

Total Price: Sum of all the Subtotals w/tax

Average Price: Average of the Subtotal w/tax column

Total Quantity: Sum of the Qty column

Completed Bill of Materials (BOM):

Here is the completed BOM. Check that all of the highlighted values are correct:

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:

timing_template.xlsx

Naming Variables:

Start by creating a column of variable names. Variable names must be:

• Unique

• No spaces

• Case Sensitive

• Avoid names such as A1, since it will be confused with cell A1.

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

• • Volume (Vol) is defined as: =A*t

  • Calculated mass (M_calc) is defined as: =Vol*p

  • % error Mass (M_error)is defined as: =(M_act-M_calc)/M_act*100

Finally, to verify and double check our equations, toggle the formulas by pressing 'CTRL' and '~'

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

1 printout showing the equations with named variables (use ctrl ~)

See additional Excel Tips: here