ky
play

KY San Jose State University Engineering 10 1 Plotting in Excel - PowerPoint PPT Presentation

Nov 03, 2023 •324 likes •564 views

KY San Jose State University Engineering 10 1 Plotting in Excel Select Insert from the main menu Select All Chart Types San Jose State University Engineering 10 2 Column Chart Definition : A chart that consists of multiple columns

  1. KY San Jose State University Engineering 10 1

  2. Plotting in Excel Select Insert from the main menu Select All Chart Types San Jose State University Engineering 10 2

  3. Column Chart • Definition : A chart that consists of multiple columns (vertical bars), and the height of each column represents the quantity associated with the corresponding category. • Use this type of chart for visual comparison of the quantities associated with different categories San Jose State University Engineering 10 3

  4. Column Chart - Example Select Insert → Column Chart → 2D Column Select data range Category No. of Student Frosh 4000 Sophomore 3500 Junior 6800 Senior 7000 Graduate 8000 San Jose State University Engineering 10 4

  5. Column Chart - Example Chart Layout options Select the option with Chart Title and Axes Title San Jose State University Engineering 10 5

  6. Column Chart - Example Edit the chart and axes titles, place the curser on the text and right click A plot must have a title and a description of the axes, including units if applicable San Jose State University Engineering 10 6

  7. Multi-Column Chart - Example Grade 2001 2007 Multiple Column Charts are used Frosh 3500 4000 for Comparison, select all three Sophomore 3000 3500 columns. Include title, description Junior 6250 6800 for axes and modify the legends. Senior 6750 7000 Grad. 7250 8000 San Jose State University Engineering 10 7

  8. Pie Chart • Definition: A graph in the shape of a circle divided into sectors, whose areas correspond to the proportions of the quantities. • Application: Visual representation of relative magnitudes of a given set of quantities San Jose State University Engineering 10 8

  9. Pie Chart Label the Chart Use Chart Layout San Jose State University Engineering 10 9

  10. XY (Scatter) Plots XY plots are two dimensional graphs. Scientifically, it is a plot of independent variable ( x ) against a dependent variable ( y ). The plot is used to obtain a functional relationship between two variables There are five options in Chart sub-type menu. The first option, where only points are plotted, is the most common type used in engineering and science fields. A curve is fitted to the plotted points to obtain the function describing the relationship between the two variable. San Jose State University Engineering 10 10

  11. XY (Scatter) Plots - Example Select Chart Layout (Layout 1) San Jose State University Engineering 10 11

  12. Fitting a Curve to Data Points ( XY ) Given the plot of test results, the question is what would be your test score if you studied for 2.5 hours, no data is available for 2.5 hours. In order to answer the question, you have to find the equation relating the test score to the number of hours spent studying. San Jose State University Engineering 10 12

  13. Fitting a Curve to Data Points ( XY ) • Engineers frequently collect paired data in order to understand the characteristics or behavior of an object or a system. • The goal is to capture the overall trend reflected by the entire data set. This can be achieved by obtaining the equation of a curve (or a straight line) that best describes (fits) the data points. The Method of Least Squares is used for this purpose. • The Method of Least Squares minimizes the sum of the squares of the errors. San Jose State University Engineering 10 13

  14. Fitting a Curve to Data Points ( XY ) The Method of Least Squares can be used to fit many different types of functions through a set of data points. The data obtained in many engineering applications may be represented by a straight line, exponential function, a power function, or a polynomial. Equation Type Mathematical expression y = a x + b Linear (straight line) y = a e bx Exponential y = a x b Power y = a ln ( x ) + b Logarithmic Polynomial (up to 6 th order) In Excel k = 6 y = c 1 + c 2 x + c 3 x 2 + …… + c k +1 x k The Method of Least Squares obtains the appropriate set of values for the coefficients, a, b , c 1 , …. San Jose State University Engineering 10 14

  15. Fitting a Curve to Data Points ( XY ) Data point Select a data point and right click, then choose Add Trendline option. Select the curve to fit the data. Check the Set Intercept box to force the curve to pass through origin (0,0). Check the Display Equation on chart and the Display R-squared value on chart boxes. San Jose State University Engineering 10 15

  16. Fitting the best Curve to Data Points There are six option to select from, start with your best guess and check the fit. The best fit is indicated by how close the correlation factor, R 2 , is to unity, the closer to 1 the better the fit. Exponential Linear Polynomial (5 th order) Polynomial (3 rd order) San Jose State University Engineering 10 16

  17. Fitting the best Curve to Data Points Given the plot of test results, the question is what would be your test score if you studied for 2.5 hours, no data is available for 2.5 hours. Substitute x = 2.5 in the 5 th order polynomial equation to find y (hours spent studying). y = .2361(2.5) 5 – 4.4722(2.5) 4 +31.764(2.5) 3 – 107.53(2.5) 2 + 185(2.5) – 60 y = 75.11 (exam score for 2.5 hours of studying) Equation Type R-squared value Exam Score Exponential .675 62.9 Linear .73 65.5 Best answer Polynomial (3 rd order) .913 77.3 Polynomial (5 th order) .931 75.1 Best fit (does not make sense) San Jose State University Engineering 10 17

  18. Histogram There are times that is desirable to plot the data in a manner that shows how the values are distributed within a certain range. This type of plot is called a histogram (a relative frequency plot). An engineer is responsible for monitoring the quality of 1000-ohm resistors. To do this, the engineer must measure the resistance of a number of resistors within a batch selected at random. The results are shown below. The acceptable variation is ± 2% (980-1020). The question is how many resistors fall within the acceptable range. Sample No. Resistance, ohm Sample No. Resistance, ohm 1 1006 16 960 2 1006 17 976 3 978 18 954 4 965 19 1004 Use the Max and MIN 5 988 20 975 6 973 21 1014 functions to find: 7 1011 22 955 Smallest value = 935 8 1007 23 973 9 935 24 993 Largest value = 1045 10 1045 25 1023 11 1001 26 992 12 974 27 981 13 987 28 991 14 966 29 1013 15 1013 30 998 San Jose State University Engineering 10 18

  19. Histogram Data menu If Data Analysis (or Solver ) does not appear, select File and choose Options, select Add-Ins , click on Go and check the boxes for Analysis ToolPak and Solver San Jose State University Engineering 10 19

  20. Histogram To create a histogram, you must first subdivide the range of the data into equally spaced intervals. The first interval must start at or below the smallest data value, and the last interval must extend to or beyond the largest data value. The interval bounds is called the Bin Range in Excel. Smallest value = 935 Largest value = 1045 The acceptable variation is ± 2% (980 to1020). Create the Bin range Find the desired interval: 40 (1020-980=40) The Bin Range 900 < 935 940 Create a column in the 980 Excel for the Bin Range 1020 1060 > 1045 San Jose State University Engineering 10 20

  21. Histogram Data range (has to Select Data Analysis and be in one column) Bin range choose Histogram Select to obtain a histogram plot San Jose State University Engineering 10 21

  22. Histogram 16 data values fall in the 980 – 1020 range San Jose State University Engineering 10 22

Recommend

More recommend