Transcript Linear interpolation in excel

13/14 Semester 1
Computer Programming
(TKK-2144)
Instructor: Rama Oktavian
Email: [email protected]
Office Hr.: M.13-15, W. 13-15 Th. 13-15, F. 13-15
Outlines
1. Interpolation: Basic Theory
2. Methods of interpolation
3. Interpolation in Excel
4. Interpolation in chem.eng
Interpolation (basic theory)
Interpolation function
a function that passes exactly through a set of data points.
Interpolating functions to interpolate values in tables
find functional values at other values of the independent
variable, e.g. sin(0.63253)
Interpolation (basic theory)
Interpolation
1. Linear Interpolation
2. Polynomial Interpolation
3. Cubic Spline Interpolation
4. Lagrange Polynomial Interpolation
Interpolation methods
Linear Interpolation
Linear interpolation is obtained by passing a straight line
between2 data points
the exact function for which values are known only at a discrete set of
f ( x )  data points
g ( x )  the interpolated approximation to f ( x ) 
x 0 , x1  the data points(also referred to as interpolation points or nodes)
Interpolation methods
Linear Interpolation
In tabular form:
Interpolation methods
Linear Interpolation
If
g ( x)
is a linear function then
To pass through points
( x 0 , f ( x 0 ))
g ( x )  Ax  b
and
( x1 , f ( x1 ))
g ( x )  f ( x0 ) 
x  x0
x1  x 0
 f ( x1 ) 
f ( x0 ) 
Interpolation methods
Linear Interpolation
Advantages:
• Easy to use (homework, exams)
• Exact for linear functions
Disadvantages:
• Not very accurate for
nonlinear functions
Interpolation methods
Linear Interpolation
Sin(0.63) = ??
Interpolation methods
Error in Linear Interpolation
Error is defined as:
Interpolation methods
Polynomial Interpolation
For N data points, there is a unique polynomial (usually of order n-1)
that goes through each point
This is an interpolating polynomial, because it goes exactly through each
data point
Two steps:
• obtain polynomial coefficients
• evaluate the value of the polynomial at the desired location (xi)
Interpolation methods
Polynomial Interpolation
First step:
• obtain polynomial coefficients
For general n, the goal is to determine the coefficients a1, a2,…,an so
that p ( x )  y for all i = 1:n
n 1
i
i
Pn-1(x) = a1 + a2x + a3x2 +…+ anxn-1
Writing these equations in matrix-vector form, we obtain
Vandermonde matrix
Interpolation methods
Polynomial Interpolation
Second step:
• evaluate the value of the polynomial at the desired location (xi)
p n 1 ( x i )  y i
Pn-1(xi) = a1 + a2xi + a3xi2 +…+ anxin-1
Sin(0.63) = ??
Interpolation in excel
Lookup Tables
• Often, we need to retrieve data that is stored in a table
• For example, consider these tables:
Interpolation in excel
LOOKUP(Lookup_value,Lookup_vector,Result_vector)
search down
the lookup
vector
find either
a matching
value, or
a value
greater than
the lookup
value, whichever
occurs first
go to the same position in
the result vector, and extract
the value there
Interpolation in excel
Metal
Modulus of
Elasticity, psi
Density, lb/in3
Yield Strength, psi
Aluminum 2014-T6
10,600,000
0.101
60,000
Aluminum 6061-T6
10,000,000
0.098
37,000
Stainless Steel 304
28,000,000
0.284
30,000
Structural Steel A36
29,000,000
0.284
36,000
Since the properties to be retrieved are in columns, the table is called a
vertical lookup table
The vertical lookup table command in Excel is:
VLOOKUP(lookup value, table range, column number, true/false)
Interpolation in excel
VLOOKUP(Lookup_value,Table_array,col_index_num,Range_lookup)
Which column number for
extraction of result? 1, 2, 3 or 4?
search
down
left
column
of
table
array
to
find
match
optional argument:
if FALSE, requires
an exact match
result
Interpolation in excel
Examples
Interpolation in excel
Examples
Using VLOOKUP to find the freezing point of a 33% solution
The formula
=VLOOKUP(F3,$A$3:$D$54,2,0)
Interpolation in excel
Other ways to Lookup Tables
 Match(Lookup_value, lookup_array, match type)
Returns the relative position in the array that matches the
lookup value.
 Index(range, position)
Returns the value from the range in the position specified
Interpolation in excel
Example
=MAX($B$5:$B$l6)
83119
Interpolation in excel
Example
use the MATCH function to find the position of the maximum value in
the range
MATCH(lookup_value,lookup_array,match_type_num)
=MATCH(83119,$B$5:$B$16,0)
Interpolation in excel
Example
use the INDEX function to return the value in the same position in
the array of months:
=INDEX( $A$5:$A$16,4)
=INDEX( $A$5:$A$16, MATCH( MAX( $B$5: $B$16), $B$5:$B$16,0))
Interpolation in excel
Lookup value for two-way tables
Interpolation in excel
Lookup value for two-way tables
=VLOOKUP(Temp,Table, MATCH( Percent, P-Row, 1 )+ 1,1)
=VLOOKUP( M2, $A$4:$1$32, MATCH( N2, $B$3:$K$3,1 )+1, 1)
Interpolation in excel
A Note About Lookup Tables
 Lookup tables will not interpolate values!
 When looking up a numerical value, if an exact match is not
found (and the “TRUE” option allows for an approximate
match), then the value searched for is rounded down to the
next tabulated value.
 We will illustrate this in the following example
Interpolation in excel
Example
 The population data of a town is
given in the table
 We want to use a lookup table to
report the population for any year
entered
Interpolation in excel
Example
 Note that using the VLOOKUP function (with the
TRUE/FALSE option left off or set to TRUE) returns the
population for the next year in the table lower than the
input value:
Interpolation in excel
Linear interpolation in excel
For each population interval, construct an IF statement to see if the
input year falls within the interval range…
If the input year is within the interval, then the interpolation formula
should be used…
If the input year is not within the interval, then a space is entered
into the cell to make it blank.
Interpolation in excel
Linear interpolation in excel
 IF statements add a great deal of flexibility to Excel
spreadsheets – allow control over how calculations are
performed
 Lookup tables are convenient for accessing data that is
stored in table form. Important to remember that lookup
tables do not interpolate between values
 Linear interpolation formulas can be added to tables in
order to provide a better estimate of values between
tabulated points
Interpolation in excel
Application of VLOOKUP: Linear interpolation
from a table of physical properties
For any %HNO3
from 0 to 90, we
want an automatic
estimate of the
heat capacity
Interpolation in excel
Application of VLOOKUP: Linear interpolation
from a table of physical properties
Create index columns to the right and
left of the table
Interpolation in excel
Application of VLOOKUP: Linear interpolation
from a table of physical properties
Enter VLOOKUP functions based on “NitricTable2” and using the low and
high row indices to extract the bracketing values of %HNO3 and Heat
Capacity.
Interpolation in excel
linear approximation
between to locations
in the table
LowHC
HeatCap
estimate
HighHC
LowPct
H eatC ap 
 input  L ow P ct 
 H ighP ct  L ow P ct 
input
%HNO3
HighPct
  H ighH C  L ow H C   L ow H C
Interpolation in excel
Automatic linear interpolation
formula and results
Interpolation in excel
Change values of the input to get different results
[including bad results when input outside of
acceptable range]
the good
the bad
and the ugly!
Interpolation in excel
Linear Interpolation using TREND function
TREND can be used to perform linear interpolation between two
adjacent data points.
TREND( known_y's, known_x's, new_x 's, const)
=TREND( B20: B21 , A20:A21, F18,1)
Interpolation in excel
Linear Interpolation in a Two-way Table
Find the viscosity value in the
table for x = 76°F, y = 56.3 wt%
ethylene glycol
Interpolation in excel
Linear Interpolation in a Two-way Table
First step, interpolate the value at y = 56.3
Second step, interpolate the value at x = 76
Extrapolation
Definition
The process of taking data values at points x1, ..., xn, and approximating a value
outside the range of the given points.
The process of estimating and forecasting
Sin (2.6) = ??
Extrapolation
Methods
Linear extrapolation
If the two data points nearest the point
linear extrapolation gives the function:
Polynomial extrapolation
to be extrapolated are
and
,
Extrapolation
Methods
Linear extrapolation
Given the following data which is known to be linear, extrapolate the y
value when x = 2.3.
(0.3 0.80), (0.7, 1.3), (1.2, 2.0), (1.8, 2.7)
Interpolation in Chem. Eng
Example - Vapor Pressure of Water
Determine vapor pressure of water
at:
• 25 °C
• 92 °C
• 105 °C
Interpolation in Chem. Eng
Example - “Real” Gases
p Vˆ  zRT
p- pressure
z- compressibility factor
R- gas constant
T- temperature
Vˆ - Molar volume
Find z at T = 725K and p = 8bar.