Interpolation of column data

[roscoe]

Column "A" contains unevenly spaced x values (8.9, 9.5, 10.2, etc...)
Column "B" contains the measured Y values.

Now, repeat that 5 more times for a total of 6 pairs of X,Y data (six different conditions) where the "X" values are not sync'ed (i.e. the next set of x values differs from the first such as 8.8, 9.4, 10.3, etc...). I'm trying to create a common "X" reference using evenly spaced data (8.0, 9.0, 10.0, etc...). This requires interpolating between the values of each data set.

I understand linear interpolation...what I can't figure out is the easiest way to find the correct values of "X" that bracket the value of interest (e.g. 9.0 would require interpolating between the values at 8.9 and 9.5, but I gotta find them first.)

I figure a lookup will give me the lower one, and an offset will provide the next, but I can't figure out the mechanics of making that happen.

Ideas?

 

[c_m]

something like this?

<title>Excel Jeanie HTML</title><table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td style="font-weight: bold; text-align: center;" colspan="2">Set 1</td> <td style="font-weight: bold; text-align: center;" colspan="2">Set 2</td> <td style="font-weight: bold; text-align: center;" colspan="2">Set 3</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="font-weight: bold; text-align: center;">X</td> <td style="font-weight: bold; text-align: center;">Y</td> <td style="font-weight: bold; text-align: center;">X</td> <td style="font-weight: bold; text-align: center;">Y</td> <td style="font-weight: bold; text-align: center;">X</td> <td style="font-weight: bold; text-align: center;">Y</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="text-align: right;">8.9</td> <td style="text-align: right;">13.12</td> <td style="text-align: right;">8.8</td> <td style="text-align: right;">16.04</td> <td style="text-align: right;">8.5</td> <td style="text-align: right;">5.59</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="text-align: right;">9.5</td> <td style="text-align: right;">14.95</td> <td style="text-align: right;">9.4</td> <td style="text-align: right;">3.38</td> <td style="text-align: right;">9.1</td> <td style="text-align: right;">7.10</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="text-align: right;">10.2</td> <td style="text-align: right;">4.43</td> <td style="text-align: right;">10.3</td> <td style="text-align: right;">17.46</td> <td style="text-align: right;">10.7</td> <td style="text-align: right;">4.85</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="text-align: right;">11.1</td> <td style="text-align: right;">19.22</td> <td style="text-align: right;">11.4</td> <td style="text-align: right;">13.95</td> <td style="text-align: right;">11.3</td> <td style="text-align: right;">2.42</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="text-align: right;">12.3</td> <td style="text-align: right;">0.39</td> <td style="text-align: right;">12.5</td> <td style="text-align: right;">4.40</td> <td style="text-align: right;">13</td> <td style="text-align: right;">18.96</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td style="font-weight: bold; text-align: center;">X</td> <td style="font-weight: bold; text-align: center;">Y (Set 1)</td> <td style="font-weight: bold; text-align: center;">Y (Set 2)</td> <td style="font-weight: bold; text-align: center;">Y (Set 3)</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td style="text-align: right;">8.0</td> <td style="text-align: right;">17.03</td> <td style="text-align: right;">14.37</td> <td style="text-align: right;">2.23</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td style="text-align: right;">9.0</td> <td style="text-align: right;">14.28</td> <td style="text-align: right;">13.03</td> <td style="text-align: right;">4.43</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td style="text-align: right;">10.0</td> <td style="text-align: right;">11.52</td> <td style="text-align: right;">11.69</td> <td style="text-align: right;">6.64</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td style="text-align: right;">11.0</td> <td style="text-align: right;">8.77</td> <td style="text-align: right;">10.35</td> <td style="text-align: right;">8.84</td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td> <td style="text-align: right;">12.0</td> <td style="text-align: right;">6.02</td> <td style="text-align: right;">9.00</td> <td style="text-align: right;">11.05</td> <td> </td> <td> </td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>B10</td> <td>=TREND($B$3:$B$7,$A$3:$A$7,$A10)</td></tr> <tr> <td>C10</td> <td>=TREND($D$3:$D$7,$C$3:$C$7,$A10)</td></tr> <tr> <td>D10</td> <td>=TREND($F$3:$F$7,$E$3:$E$7,$A10)</td></tr></tbody></table></td></tr></tbody></table>
Copy B10, C10 and D10 down.

or do you want the average of the Y-values for all sets?

 

[tusharm]

You may also want to see
Interpolation
http://www.tushar-mehta.com/excel/newsgroups/interpolation/

 

[roscoe]

You may also want to see
Interpolation
http://www.tushar-mehta.com/excel/newsgroups/interpolation/

That worked...key was the use of MATCH and INDEX.

Thanks

 

[mikerickson]

You might want to look at this thread
http://www.mrexcel.com/forum/showthread.php?t=365486&highlight=interpolation