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

San Jose State University

Engineering 10 1

KY

Plotting in Excel

San Jose State University

Engineering 10 2

Select Insert from the main menu Select All Chart Types

San Jose State University

Engineering 10 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 4

Column Chart - Example

Category

• No. of Student

Frosh 4000 Sophomore 3500 Junior 6800 Senior 7000 Graduate 8000 Select Insert → Column Chart → 2D Column Select data range

San Jose State University

Engineering 10 5

Column Chart - Example

Chart Layout options Select the option with Chart Title and Axes Title

San Jose State University

Engineering 10 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 7

Grade 2001 2007 Frosh 3500 4000 Sophomore 3000 3500 Junior 6250 6800 Senior 6750 7000 Grad. 7250 8000

Multi-Column Chart - Example

Multiple Column Charts are used for Comparison, select all three

• columns. Include title, description

for axes and modify the legends.

San Jose State University

Engineering 10 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
• f a given set of quantities

San Jose State University

Engineering 10 9

Use Chart Layout

Pie Chart

Label the Chart

San Jose State University

Engineering 10 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 11

XY (Scatter) Plots - Example

Select Chart Layout (Layout 1)

San Jose State University

Engineering 10 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 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 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 = ax + b

Linear (straight line)

The Method of Least Squares obtains the appropriate set of values for the coefficients, a, b, c1, ….

Polynomial (up to 6th order)

y = c1 + c2x + c3x2 + …… + ck+1xk

In Excel k = 6

Exponential

y = aebx

Power

y = axb

Logarithmic

y = a ln(x) + b

San Jose State University

Engineering 10 15

Fitting a Curve to Data Points (XY)

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.

Data point

San Jose State University

Engineering 10 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, R2, is to unity, the closer to 1 the better the fit.

Linear Exponential Polynomial (3rd order) Polynomial (5th order)

San Jose State University

Engineering 10 17

Fitting the best Curve to Data Points

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)

Substitute x = 2.5 in the 5th order polynomial equation to find y (hours spent studying).

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.

Linear Exponential Polynomial (3rd order) Polynomial (5th order) Equation Type Exam Score R-squared value .73 .675 .913 .931 75.1 77.3 62.9 65.5 Best fit (does not make sense) Best answer

San Jose State University

Engineering 10 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 5 988 20 975 6 973 21 1014 7 1011 22 955 8 1007 23 973 9 935 24 993 10 1045 25 1023 11 1001 26 992 12 974 27 981 13 987 28 991 14 966 29 1013 15 1013 30 998

Largest value = 1045 Smallest value = 935 Use the Max and MIN functions to find:

San Jose State University

Engineering 10 19

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

Histogram

Data menu

San Jose State University

Engineering 10 20

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.

Histogram

Largest value = 1045 Smallest value = 935 The acceptable variation is ± 2% (980 to1020). Find the desired interval: 40 (1020-980=40) 940 The Bin Range 980 1020 900 < 935 1060 > 1045 Create a column in the Excel for the Bin Range

Create the Bin range

San Jose State University

Engineering 10 21

Histogram

Data range (has to be in one column) Bin range Select to obtain a histogram plot Select Data Analysis and choose Histogram

San Jose State University

Engineering 10 22

Histogram

16 data values fall in the 980 – 1020 range