Week 13 Spreadsheets for Engineering Applications part 1 BJ Furman 16APR2010 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 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 Graphical Presentation in Engineering Presenting data in graphical form is extremely important A picture really is worth a thousand words Excel or other spreadsheet program offers very powerful easy to use graphical presentation tools Get good at using it 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 o o o o Line Chart x axis data varies continuously o x axis data will be equally spaced on the chart beware If the actual x Actual x axis data that is unequally spaced will be plotted properly data is not equally spaced the plot will be misleading good for analyzing trend in data most often used for engineering analysis See XY Scatter vs Line Chart xls 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 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 transtekinc com assets images 240ACTION gif Calibration of LVDT Sensor Method used by LeroyCrandall Geotechnical Laboratory LVDT body http gees usc edu soilab Calibration htm LVDT core core motion Micrometer head http gees usc edu soilab Photos Calibration 20Pictures mvc 159f jpg Analyze LVDT Calibration Data curve fit xls Plot data Add trend line with the wizard Get the trend line using LINEST returns an array formula must be entered as an array cntrl shift enter output array is 5 x ny data sets 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 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 Example LVDT dataset for lecture txt 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 yvalues Example voltage divider xls 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 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 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 Numerical Integration Trapezoidal integration Area Under 3 2cos x 10 5 4 5 Ex area under a curve 4 3 5 3 2 5 2 y 3 2 cos 10 1 5 x 1 0 5 0 0 0 5 1 1 5 2 2 5 3 3 5 4 4 5 5 Numerical Integration cont Divide into trapezoids Calculate the area of the trapezoids y yi 1 Ai i xi 1 xi 2 Sum areas Voila Results Exact 21 3661977 Numerical 21 3137515 Numerical Differentiation First derivatives Larsen 2009 Numerical Differentiation cont Second derivatives Larsen 2009 Review 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 13601775 4 Engineering with Excel companion website http www chbe montana edu excel EngExcel3 htm Visited 25OCT2009