Excel formula : Interpolate in a second column between dynamic repeating integer values in the first column.
In one column of an Excel sheet I have an ascending series of integer values, which increment after several rows. In the next column I wish to use a formula based solution to generate evenly distributed values, creating between one integer and the next. The increment will depend on how many rows it takes for the integer in the first column to increase by 1.
I'm pretty good with Excel, but this one has really stumped me - nothing I have produced does it so far.
If the first column is static, that is easy enough, but as it isn't I'm struggling.
Could anyone point me in the right direction of a suitable formula.
Here's what I should get:
<html>
<table width:156pt>
<col width:82pt>
<col width:74pt>
<tr>
<td>Column
A</td>
<td>Column B</td>
</tr>
<tr >
<td>(These values are from a lookup table, and can change, so
the formulas in Column B need to account for this).</td>
<td>(This is where the formula is needed).</td>
</tr>
<tr>
<td >1</td>
<td >1</td>
</tr>
<tr>
<td >1</td>
<td >1.2</td>
</tr>
<tr>
<td >1</td>
<td >1.4</td>
</tr>
<tr>
<td >1</td>
<td >1.6</td>
</tr>
<tr>
<td >1</td>
<td >1.8</td>
</tr>
<tr>
<td>2</td>
<td>2</td>
</tr>
<tr>
<td>2</td>
<td>2.33</td>
</tr>
<tr>
<td>2</td>
<td>3.67</td>
</tr>
<tr>
<td>3</td>
<td>3</td>
</tr>
<tr>
<td>3</td>
<td>3.25</td>
</tr>
<tr>
<td>3</td>
<td>3.5</td>
</tr>
<tr>
<td>3</td>
<td>3.75</td>
</tr>
<tr>
<td>4</td>
<td>4</td>
</tr>
</table>
</html>Thanks
