Spreadsheet engineering analyses the good the bad and the ugly
This presentation is the property of its rightful owner.
Sponsored Links
1 / 12

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


  • 98 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

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

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


  • Login