I have data tables like this example, nine entries at A1:B9 in this case:

```
A B
-- ---
1 2.9
2 5.06
3 7
4 8.84
5 10.87
6 13.24
7 16.22
8 20.25
9 36.7
```

The above represents nine measurements of a non linear increasing physical variable in B, Voltage for example, and A represents exactly each one of nine round minutes the measurement was done.

I want to create a second table, columns E and F, with a quantity of rows that is the "next integer" for the highest value in the B column. In this case, B9=36.7, thus it will have 37 rows. Column F1:F37 will contain integers 1 to 37, column E must have numeric values that correspond to F, in the same relationship as between columns A to B. In other words, interpolate the column E values corresponding to the column F values.

For example, A3=3 and B3=7. In this case, F7=7 and E7=3 because B already included the integer 7 and has a matching value in column A. However, F8=8, which is an intermediate value not contained in column B. So E8 will lie between 3 and 4, based on the original data, and must be interpolated.

The idea is when plotting a graph, A1:B9 will have the same shape as E1:F37. In this example, I will expand the data table to 37 integer results that would have occurred over the course of the original measurements, and will see what time (in column E, with decimal places), those values would have occurred.

### What I've tried

In trying to solve this myself, I was able to find a time consuming formula (note that in my attempt, my E and F columns are reversed from what I described above).

- I created a column (K) containing the difference between the B column elements. K5 = B5-B4. That is the Y displacement for every X increment.
- Column E will contain as many sequential integer numbers (37), starting at 1, as the next integer value of the biggest element in B. In this case, B9 contains 36.7, so 37.
- On F1:F37 I input the following formula.

Cell F1 contains:

```
=IF(E1>$B$9,$A$9+(E1-$B$9)/$K$9,IF(E1>$B$8,$A$8+(E1-$B$8)
/$K$9,IF(E1>$B$7,$A$7+(E1-$B$7)/$K$8,IF(E1>$B$6,$A$6+(E1-$B$6)
/$K$7,IF(E1>$B$5,$A$5+(E1-$B$5)/$K$6,IF(E1>$B$4,$A$4+
(E1-$B$4)/$K$5,IF(E1>$B$3,$A$3+(E1-$B$3)/$K$4,IF(E1>$B$2,$A$2+
(E1-$B$2)/$K$3,IF(E1>$B$1,$A$1+(E1-$B$1)/$K$2,E1/$K$1)))))))))
```

It works pretty well. But it is not an automated formula; one must enter as many "IFs" as elements in the columns A+B (X+Y). I tested scatter charts with lines from A1:B9 and E1:F37 (reversed for correct X/Y sequence), and they generated exactly the same curve shape, so it works.

But it is not an effective solution because it requires a tedious, custom, manual process for each data set. I'm looking for a way to accomplish this in a more automated way with features built into Excel, or at least a more generic approach using formulas.

`TREND`

function to calculate the Y values – Ron Rosenfeld Oct 11 '15 at 20:04