Spreadsheet Engineering Analyses : The Good, The Bad, and The Ugly

1. Spreadsheet Engineering Analyses:The Good, The Bad, and The Ugly D Raj Raman January 23rd, 2013

2. Why Use Spreadsheets? • Readability • neater than all but the most fastidious engineer with his or her mechanical pencil • Readily modified • Can look at “what if?” scenarios • Can easily debug • E.g., error in an assumed value

3. Why Use Spreadsheets? • Easily presented visually – fairly powerful graphing capabilities • Higher level analyses possible • Visual Basic for Applications (VBA) • Built in tools (e.g., solver) • Many add-ons available

4. An Overarching Question: How Much Detail? • Conciseness is a virtue • Excessive brevity (to the point that others cannot follow your work) is a fault! • Both these statements also apply to written engineering solutions!

5. Good Spreadsheet Practice #1 • The only numerical values you should enter are givens or assumptions • These should be clearly listed together on a common area or tab of the workbook • They should be given with units (or explicitly listed as dimensionless) • All other values should be calculated • And calculations should be done so that intermediate steps are clearly visible (with units) for crosschecking

6. Good Spreadsheet Practice #2 • When possible, keep contents visible on a single desktop (or page) • Use multiple tabs to handle larger projects (like ours) • Realize that for some data intensive problems, this won’t work • Use “Freeze Panes” under “Window” menu to ensure that headings and row titles are maintained on large sheets

7. Good Spreadsheet Practice #3 • Make the sheet itself visually informative • Color • Fonts • Conditional Formatting • Present only a reasonable number of significant digits • Select units carefully and convert appropriately • Use scientific notation

8. Good Spreadsheet Practice #4 • Be absolutely confident in your use of relative and conditional addressing • Screwing this up leads to major errors!

9. Good Spreadsheet Practice #5 • If there are values (e.g., physical constants, conversion factors) that recur in many equations on your sheet, name them rather than using absolute addressing • Much easier to read and debug cell equations if you do this

10. Good Spreadsheet Practice #6 • Make your graphs consistent • Make your first graph and spend time getting the formatting correct • graph should be separate sheet of the workbook • Copy the formatted graph repeatedly • Change the cell references in the graph sheet

11. Bad Spreadsheet Practices • Insufficient detail • Computed values entered by hand, based on calculator • Intermediate calculations not shown • Excessively long equations • Constants not given names • Conversion factors not listed explicitly • Assumptions listed multiple times • No “one stop” changing of assumptions

12. Ugly Spreadsheet Practices • Givens, assumptions, intermediate, and final computed values intermixed • Flow unclear • Lack of spatial organization • Poor font selections and no use of font or color to delineate information • Excessive significant figures • Row and column headings not “frozen” for large sheets • On graphs, variations in font size, font selection, line width, etc. look amateurish