Transcript EGR 105 - University of Rhode Island

EGR 105
Foundations of Engineering I
Fall 2008 – Session 4
Excel – Plotting, Curve-Fitting, Regression
EGR105 – Session 4 Topics
•
•
•
•
•
•
Review of Basic Plotting
Data Analysis Concepts
Regression Methods
Example Function Discovery
Regression Tools in Excel
Homework Assignment
Analysis of x-y Data
• Independent versus dependent variables
y = f (x)
dependent
y
x
independent
Simple Plotting
Generate X and Y data to Plot
Common Types of Plots: Y=3X2
350
Normal
300
250
1000
200
Y
log-log: log y-log x
150
100
logY
100
50
0
0
2
4
6
8
10
10
X
1
350
1
Semi-log: log x
300
10
logX
250
Y
200
y = 3x2
150
100
logy = log3 + 2logx
Straight Line on log-log Plot!
50
0
1
10
logX
Finding Other Values
• Interpolation
– Data between known points
• Regression – curve fitting
– Simple representation of data
– Understand workings of system
– Useful for prediction
• Extrapolation
– Data beyond the measured range
data
points
Curve-Fitting - Regression
• Useful for noisy or uncertain data
– n pairs of data (xi , yi)
• Choose a functional form y = f(x)
• polynomial
• exponential
• etc.
and evaluate parameters for a “close” fit
What Does “Close” Mean?
• Want a consistent rule
• Common is the least squares fit (SSE):
y
(x3,y3)
e3
(x1,y1)
(x2,y2)
(x4,y4)
ei = yi – f(xi),
i =1,2,…,n
n
SSE   ei
2
i 1
x
Quality of the Fit:
n
SSE   ei
2
SSE
R  1
SST
2
y
i 1
yy
n
SST   ( yi  y ) 2
i 1
Notes:
x
y is the average y value
0  R2  1
closer to 1 is a “better” fit
Linear Regression
• Functional choice y = m x + b
slope
• Squared errors sum to
SSE    yi  m xi  b
2
i
• Set m and b derivatives to zero
 SSE
0
m
 SSE
0
b
intercept
Further Regression Possibilities:
• Could force intercept: y = m x + c
• Other two parameter ( a and b ) fits:
– Logarithmic:
– Exponential:
– Power function:
y = a ln x + b
y = a e bx
y=axb
• Other polynomials with more parameters:
– Parabola:
– Higher order:
y = a x2 + bx + c
y = a xk + bxk-1 + …
Excel’s Regression Tool
• Highlight your chart
• On chart menu, select “add trendline”
• Choose type:
– Linear, log, polynomial, exponential, power
• Set options:
–
–
–
–
Forecast = extrapolation
Select y intercept
Show R2 value on chart
Show equation on chart
Linear & Quartic Curve Fit Example
7
y = 0.996x
R² = 0.986
6
5
Y
4
3
2
1
X
0
0
1
2
3
4
5
6
7
7
6
y = 0.037x4 - 0.523x3 + 2.518x2 - 3.878x + 3.133
R² = 0.997
5
Y
4
Better fit but does it
make sense with
expected behavior?
3
2
1
0
0
1
2
3
4
5
6
7
X
Example Function Discovery
How to find the best relationship
• Look for straight lines on log axes:
 linear on semilog x  y = a ln x + b
 linear on semilog y  y = a e bx
 linear on log log
y=axb
• No rule for 2nd or higher order polynomial
fits
Previous EGR105 Project
Discover how a pendulum’s timing is impacted
by the:
– length of the string?
– mass of the bob?
1. Take experimental data
– string, weights, rulers, and watches
2. Analyze data and “discover” relationships
Experimental Setup:
One Team’s Results:
length (inches)
time (sec)
121.5
114.0
105.0
97.0
85.0
79.0
67.5
58.5
50.0
43.0
13.0
13.73
3.5
3.4
3.3
3.1
2.9
2.8
2.6
2.4
2.3
2.1
1.2
mass (grams)
27.47 41.20 54.94
3.5
3.5
3.5
3.4
3.4
3.4
3.3
3.3
3.3
3.1
3.1
3.1
2.9
2.9
2.9
2.8
2.8
2.8
2.6
2.6
2.6
2.4
2.4
2.4
2.3
2.3
2.3
2.1
2.1
2.1
1.2
1.2
1.2
Mass
appears to
have no
impact, but
length does
To determine the effect of length, first plot
the data:
4.0
time (seconds)
3.5
3.0
2.5
2.0
1.5
1.0
0.5
0.0
0.0
20.0
40.0
60.0
80.0
length (inches)
100.0
120.0
140.0
Try a linear fit:
4.0
time (seconds)
3.5
3.0
2.5
2.0
1.5
y = 0.02x + 1.1692
R2 = 0.9776
1.0
0.5
0.0
0.0
20.0
40.0
60.0
80.0
length (inches)
100.0
120.0
140.0
Force a zero intercept:
time (seconds)
4.5
4.0
3.5
3.0
2.5
2.0
1.5
y = 0.0332x
R2 = 0.4832
1.0
0.5
0.0
0.0
20.0
40.0
60.0
80.0
length (inches)
100.0
120.0
140.0
Try a quadratic polynomial:
4.0
time (seconds)
3.5
3.0
2.5
2.0
1.5
y = -0.0002x2 + 0.0551x
R2 = 0.9117
1.0
0.5
0.0
0.0
20.0
40.0
60.0
80.0
length (inches)
100.0
120.0
140.0
Try logarithmic:
4.0
time (seconds)
3.5
3.0
2.5
2.0
1.5
y = 1.0349Ln(x) - 1.6506
R2 = 0.9609
1.0
0.5
0.0
0.0
20.0
40.0
60.0
80.0
length (inches)
100.0
120.0
140.0
Try power function:
4.0
time (seconds)
3.5
3.0
2.5
2.0
1.5
y = 0.3504x0.4774
R2 = 0.9989
1.0
0.5
0.0
0.0
20.0
40.0
60.0
80.0
length (inches)
100.0
120.0
140.0
On log-log axes, a nice straight line:
time (seconds)
10.0
t  alb  log(t )  log(a)  b log(l )
b
1.0
1.0
10.0
100.0
length (inches)
Power Law Relation:
1000.0
Elastic Bungee Cord Models Determined
by Curve Fitting the Data
• Linear Model (Hooke’s Law):
F (s)  ks
• Nonlinear Cubic Model: F (s)  k1s  k2 s 2  k3s3
Force (lb)










Collected Data
Cubic Fit
Better and it Makes
Sense with the Physics
Linear Fit
l  lo
Elongation

s
OriginalLength
lo
Homework Assignment
See passed out sheet or course web site