Lecture 11: Spreadsheets for Engineering Applications – part 1

1. Lecture 11: Spreadsheets for Engineering Applications – part 1 BJ Furman 16APR2012

2. The Plan for Today • Spreadsheets for engineering applications - part 1 • Charting and data presentation • Example: LVDT calibration • Adding a trend line to data • Numerical integration • Numerical differentiation

3. Learning Objectives • Create effective charts and graphs • Explain the difference between a line chart and a scatter plot • Add and format secondary axes • Annotate and format a chart for presentation in a written report • Find the area under a curve using trapezoidal integration

4. Graphical Presentation in Engineering • Presenting data in graphical form is extremely important! • A picture really is worth a thousand words! • Especially for engineering! • Excel (or other spreadsheet program) offers very powerful, easy to use graphical presentation tools. • Get good at using it!

5. LVDT Sensor • Linear Variable Differential Transformer • non-contact, friction-free position sensor • infinite resolution • absolute position measurement • robust • Need to calibrate • Measure output voltage as the core is moved known amounts • Plot voltage vs. displacement http://www.macrosensors.com/images/tutorial_page_images/images/fig1.jpg http://www.rdpe.com/us/hiw-lvdt.htm http://www.transtekinc.com/assets/images/240ACTION.gif

6. LVDT application – Road Simulator http://www.swenox.com/gtc/images/4-axis-durability-rig.jpg

7. LVDT body Calibration of LVDT Sensor • Method used by Leroy-Crandall Geotechnical Laboratory http://gees.usc.edu/soilab/Calibration.htm core motion LVDT core Micrometer head http://gees.usc.edu/soilab/Photos/Calibration%20Pictures/mvc-159f.jpg

8. XY Scatter vs. Line Chart • What is the difference? • Different treatment of the x-axis data • XY Scatter Chart: for x data that varies continuously • Interpolating between points makes sense • Ex. temperature vs. time over 24 hrs • Line Chart: for x data that is categorical or equally spaced • Interpolating between points may not make sense • Ex. average lab report score for Tues, Wed, Thurs sections XY Scatter Chart • x-axis data varies continuously • Actual x-axis data that is unequally spaced will be plotted properly • good for analyzing trend in data • most often used for engineering analysis Line Chart • x-axis data will be equally spaced on the chart (beware!). If the actual x data is not equally spaced, the plot will be misleading. See: XY Scatter vs. Line Chart.xls

9. XY Scatter vs. Line Chart, cont. • Smoothed line or not? • Generally, not • Smoothed line can be misleading unless generating function is a good representation of actual behavior of the data • Better to leave as points or fit a regression line/curve that is a likely candidate to describe the underlying behavior right-click | Format Data Series | smoothed line check-box

10. Analyze LVDT Calibration Data Displ. (mm) A/D signal (V) 5.08 8.0188 4.572 7.5003 4.064 6.756 3.556 5.9277 3.048 5.1086 2.54 4.2706 2.032 3.4 1.524 2.5562 1.016 1.6895 0.508 0.8319 0 -0.0256 -0.508 -0.9036 -1.016 -1.752 -1.524 -2.6273 -2.032 -3.4366 -2.54 -4.3106 -3.048 -5.1587 -3.556 -5.976 -4.064 -6.7865 -4.572 -7.4911 -5.08 -8.0548 • curve fit.xls • Plot data • Add trend line with the wizard • Get the trend line using SLOPE(), INTERCEPT(), OR LINEST( ) • returns an array • formula must be entered as an array • cntrl+shift+enter • output array is 5 x nindep_var +1 data sets

11. LINEST() statistics

12. Adding Data Series • Cases where you might have additional data to add to a chart • Calculations on a data set • Multiple data sets • Right-click in the plot region • ‘Select Data’ (2007) or ‘Source data’ (2003) • Add a data series • Name • X values • Y values • If x values are the same as previous, can just cut-and-paste • Example: LVDT_dataset_for_lecture.txt

13. Adding a Secondary Y-axis • Sometimes it is useful to plot multiple data sets on the same graph that have the same x-values, but vastly different y-values. • Example: voltage divider.xls

14. Adding Names to Ranges 2003: Insert / Name / Create / Left column 2007: Formulas / Defined Names tab / Create from Selection / Left column Highlight names and values Result

15. Formatting Your Chart • Default is probably not the best! • Publication or presentation? • Publication • No fill of chart background • No fill of data point markers • B&W markers, lines, annotation • Use line types that can be differentiated in a B&W photocopy • Maximize chart area • For landscape orientation, title goes closest to the spine • Annotate well • Descriptive title • Labeled axes with units!! • Error bars with measured data

16. Creating a Figure • Maximize the information transfer • What will the busy (or lazy) reader actually read of your report? • Structure of figure annotation: • Figure number • Figure title • Figure caption • Really important and often overlooked!

17. Figure Example • Figure number • must be referred to in the report • Figure title • Descriptive • Figure caption • The key information you want the reader to understand from the figure • Note inset figure and additional annotation for clarity (Furman, 1991)

18. Numerical Integration • Trapezoidal integration • Ex. area under a curve

19. Numerical Integration, cont. • Divide into trapezoids • Calculate the area of the trapezoids • Sum areas • Voila! • Results • Exact • 21.3661977 • Numerical • 21.3137515

20. Numerical Integration (recap) • Can think of integration as finding the area under a curve • Break area up into trapezoids http://people.oregonstate.edu/~haggertr/487/integrate.htm

21. Numerical Integration Example http://www.onid.orst.edu/~haggertr/487/integrate.xls

22. Numerical Differentiation • First derivatives (Larsen, 2009)

23. Numerical Differentiation, cont. • Second derivatives (Larsen, 2009)

24. Prose vs. a Figure Which would you rather look at? Tube wall and reflector pin temperatures vs. time during the radiative heating feasibility test. The focus of the concentrator was brought into axial alignment with the tube bore at about t=1.4 s and re-adjusted at about t=5 s. The radiant flux impinging on the bore of the tube was estimated to be about 1.65 MW/m^2 from measurements made after the test. The tube wall temperature rises rapidly to about 275 ºC in comparison to the reflector pin, confirming predictions that non-contact heating using a radiant source and an internal conical reflector is indeed feasible. Or

25. References Furman, B. (June, 1991). A new, thermally controlled, non-contact rotor balancing method (Doctoral dissertation). Available from University Microfilms International (UMI No. 9205634). p. 227 Larsen, R. W. (2009). Engineering with Excel, Pearson Prentice Hall, New Jersey. ISBN 0-13-601775-4 Engineering with Excel companion website: http://www.chbe.montana.edu/excel/EngExcel3.htm. Visited 25OCT2009.