Slide 1Slide 2Slide 3Slide 4Slide 5Slide 6Slide 7Slide 8Slide 9Slide 10Slide 11Slide 12Slide 13Slide 14Slide 15Slide 16Slide 17Slide 18Slide 19Slide 20Slide 21Slide 22Slide 23Slide 24Slide 25Slide 26Slide 27Slide 28Slide 291Homework Assignment:Homework Assignment #7Excel B EGR 120 – Introduction to EngineeringMicrosoft Excel Lecture B – Creating xy graphsTrendlineEquation determined using linear regression.2Graphing with ExcelMany software packages, including Excel, have excellent graphing capabilities. However, if care is not taken to use good graphing practices and standards, the computer might easily be used as a tool to generate poor graphs. Several features related to good graphing practices are discussed below. These practices for good graphing should be used whenever graphs are generated using Excel, MathCAD, or any software.Good Graphing Practices and Graphing Terminology1.Using the best type of graph.2.Identify the independent and dependent variables.3.Label the axes and add a title to the graph.4.Choose proper calibrations for each axis.5.Display the results correctly: Markers? Lines? Connect dots? Smooth curve?6.Methods for drawing a smooth curve (for empirical data)7.Linear versus log scales and types of trendlinesExcel B EGR 120 – Introduction to Engineering31. Using the best type of graph• Excel offers many types of graphs (referred to as charts in Excel), including column, line, pie, area, scatter, and other. Additionally, each type (see menu below) also has many sub-types that can be selected. Excel B EGR 120 – Introduction to Engineering•The most common type of Excel chart used for engineering courses is the scatter graph. However, students often incorrectly select the line graph. Line graphs are used for non-numeric data on the x-axis. Examples of non-numeric data might include days of the week, college names, sports teams, etc. Scatter graphs are the best choice for most engineering applicationsAvoid line graphs unless your x-axis data is non-numeric42. Identify the independent and dependent variables.- The x-axis is used to display the independent variable.- The y-axis is used to display the dependent variable.-Note that in an equation y = f(x), the value of y depends on the value of x inserted into the function.Excel B EGR 120 – Introduction to EngineeringSo if you have data values for P and Q, which goes on the y-axis? •If you want to express P = f(Q), then P goes on the y-axis.•If you want to express Q = f(P), then Q goes on the y-axis.y = f(x)xindependentvariabledependentvariable53. Label the axes and add a title to the graph.3. Include the variable name, variable symbol, and units for each axis.4. Example:Excel B EGR 120 – Introduction to EngineeringvariableNamevariablesymbolunittitleaxis labels4. Choose proper calibrations for each axis.•Graphs with poor calibrations (numbers on the axes) are difficult to interpolate, or estimate values between the calibrations.•Use the “1, 2, 5 rule”. Pick calibrations that are 1, 2, or 5 times 10N, where N is an integer.•Excel generally does this for you automatically. Other graphing programs may not (such as MatLab or MathCAD).•Example: The same function is graphed twice below.Excel B EGR 120 – Introduction to EngineeringPoint is easy to read as (26,360)Point is hard to read75. Display the results correctly. Should you show the data points? It depends. Should you connect the points or show a smooth curve? It depends. We will consider three types of quantities to be graphed with different rules for displaying each type.A. Observed data-This type of data has no assumed form (we do not expect it to follow some mathematical formula).-Show data points and connect them (“dot-to-dot”).Excel B EGR 120 – Introduction to EngineeringExample: Annual sales are not known to follow any mathematical formula, so we simply show the data points and connect them.8B. Empirical data-This type of data is assumed to follow some mathematical formula and thus should be displayed using a “smooth curve” rather than by connecting the points.-Most engineering data will be of this type.Excel B EGR 120 – Introduction to EngineeringReference: Statics, 7th Edition,Beer & Johnston, McGraw-Hill, Inc.Example: A linear curve (straight line) is used to represent the point since the data is assumed to follow Hook’s Law, F = kx.9C. Theoretical functionsWe sometimes wish to graph a mathematical function. This requires generating a few “points” in a spreadsheet, but the points picked are not data, so use a smooth curve, but do not show the points.Excel B EGR 120 – Introduction to EngineeringExample: Graph the function v(t) = 10te-2t using Excel. In order to graph this function, we need to generate some “points”, but these aren’t data points and we could have used other points (increments of 0.25 or 0.3 perhaps), so we don’t show the points. We use a smooth curve since it is a continuous function.106. Methods for drawing a smooth curve (for empirical data) Consider three ways to draw a smooth curve.A.Visual estimation•This method is not precise and results will vary. This is what you do when you create a graph by hand.B. Method of least averages•This is a statistical method intended to generate the line (or curve) that will result in the least average error between the line and the data points.•This seems to be the best method intuitively, but the next method is preferred.C. Method of least mean squares (linear regression)•This is a statistical method intended to generate the line (or curve) that will result in the least average square error between the line and the data points.•This is generally considered to be the method that produces the line that best fits the data. This method is used by Excel when you pick “Add a trendline.”•Linear regression determines the slope, m, and the intercept, b, of the line that best fits the data.•Linear regression also determines a correlation coefficient, R. R (or R2) is used to determine if the results can be trusted. 0 < R2 < 1 in general, and the closer R2 is to 1, the better the fit. This is not a general statement, but for our purposes we will consider R2 > 0.95 to be a good fit.Excel B EGR 120 – Introduction to Engineering11How does linear regression work? Linear regression, or the method of least mean squares, is a method for determining the equation