Spreadsheet engineering analyses the good the bad and the ugly
Download
1 / 12

Spreadsheet Engineering Analyses : The Good, The Bad, and The Ugly - PowerPoint PPT Presentation


  • 139 Views
  • Uploaded on

Spreadsheet Engineering Analyses : The Good, The Bad, and The Ugly. D Raj Raman January 23 rd , 2013. 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Spreadsheet Engineering Analyses : The Good, The Bad, and The Ugly' - yaakov


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Spreadsheet engineering analyses the good the bad and the ugly

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

D Raj Raman

January 23rd, 2013


Why use spreadsheets
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


Why use spreadsheets1
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


An overarching question how much detail
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!


Good spreadsheet practice 1
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


Good spreadsheet practice 2
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


Good spreadsheet practice 3
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


Good spreadsheet practice 4
Good Spreadsheet Practice #4

  • Be absolutely confident in your use of relative and conditional addressing

    • Screwing this up leads to major errors!


Good spreadsheet practice 5
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


Good spreadsheet practice 6
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


Bad spreadsheet practices
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


Ugly spreadsheet practices
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