# Interpolation of column data

#### roscoe

##### Well-known Member
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?

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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?

Replies
1
Views
370
Replies
3
Views
355
Replies
19
Views
511
Replies
13
Views
1K
Replies
3
Views
1K

1,203,100
Messages
6,053,529
Members
444,669
Latest member
Renarian

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back