interpolation of a curve

[reinjunon]

let's say i plotted a smooth quadratic curve with 5 points. i'd like to obtain the y value for an arbitrary x value between any 2 points... is there a way to do it without using a trend line equation? what i want to get is exactly like putting a ruler onto the graph drawn and reading the y value off the curve literally.
thank you for any help~

 

[MrKowz]

Welcome to the forums!

Are you needing linear interpolation?

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">x value</td><td style=";">y value</td><td style="text-align: right;;"></td><td style=";">Linear Interpolation</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">x Value</td><td style="text-align: right;;">15</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">5</td><td style="text-align: right;;">25</td><td style="text-align: right;;"></td><td style=";">interpolated y value</td><td style="text-align: right;;">250</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">10</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">20</td><td style="text-align: right;;">400</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">30</td><td style="text-align: right;;">900</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=FORECAST(<font color="Blue">E2,INDEX(<font color="Red">B2:B6,MATCH(<font color="Green">E2,A2:A6</font>)</font>):INDEX(<font color="Red">B2:B6,MATCH(<font color="Green">E2,A2:A6</font>)+1</font>),INDEX(<font color="Red">A2:A6,MATCH(<font color="Green">E2,A2:A6</font>)</font>):INDEX(<font color="Red">A2:A6,MATCH(<font color="Green">E2,A2:A6</font>)+1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[reinjunon]

hey thanks for replying....

but my curve is kinda like cubic and no trend equation fits in...so i want a way that can read off the graph precisely just as it is plotted in excel.

 

[Lori_M]

The previous response will give interpolated values when the chart has straight lines connecting points. If you're using smooth lines instead, try the following formula given the same data setup as above (entered in cell E4 for example):

=SUM((1+1/(IRR(MMULT({0,0,2,0;0,1,0,-1;-1,4,-5,2;1,-3,3,-1},OFFSET(A$2,MATCH(E2,A$2:A$12)-2,,4)-E2))+1E-20
))^-{0;1;2;3}*MMULT({0,2,0,0;-1,0,1,0;2,-5,4,-1;-1,3,-3,1},OFFSET(B$2,MATCH(E2,A$2:A$12)-2,,4)))/2

The formula takes the four points surrounding a given x value to build a smooth cubic curve between points. To allow for data in the two end intervals, you can extend the data at each end using the formulas in the cell below:

A1: =2*A2-A3
B1:=2*B2-B3
A7:=2*A6-A5
B7:=2*B6-B5

Results are shown in the chart below plotted for each integer x value in the range. The value in cell B6 has been changed from 900 to 200, other values are unaltered.

Technical Note:

The Excel curve is an example of a Catmull-Rom Spline and these are widely used in computer graphics and animation. The formula should match the curve exactly for most practical applications where point spacing is reasonably regular. However, detailed analysis shows Excel actually uses a small adjustment if the distances between successive points differ by a factor of three or more - this stops the curve overshooting the data points too much. In this example, the formula should match except for a very small adjustment in the interval between x = 5 and 10 due to the distance from (5,25) to (20,400) being more than three times the distance from (5,25) to (10,100) as measured on the chart. VBA code that accounts for this is below but it's probably wise not to include this for estimation purposes as the exact value depends on the chart scale that is used.

http://www.excelbanter.com/showthread.php?t=216555

 

[anon8992]

Welcome to the forums!

Are you needing linear interpolation?

Excel 2007

	A	B	C	D	E
x value	y value	Linear Interpolation
x Value
interpolated y value

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]15[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"][/TD]

[TD="align: right"]250[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1

<thead>
</thead>

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #E0E0F0"]E3[/TH]
[TD="align: left"]=FORECAST(E2,INDEX(B2:B6,MATCH(E2,A2:A6)):INDEX(B2:B6,MATCH(E2,A2:A6)+1),INDEX(A2:A6,MATCH(E2,A2:A6)):INDEX(A2:A6,MATCH(E2,A2:A6)+1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi,

Im trying to learn excel, and I came accross your code above. Im just wondering if you could explain in simple terms what exactly the code above is doing?

Thanks so much for the help

A