Interpolate values between two cells

Question

I've got a spreadsheet that looks like this in Libre Office Calc:

    +-----------+
    |     |  A  |
    |  1  | 444 |
    |  2  |     |
    |  3  |     |
    |  4  |     |
    |  5  |     |
    |  n  |     |
    | n+1 | 938 |
    +-----------+

A(1) < A(n+1), 444 and 938 are used as a matter of example.

Is there a way to automatically fill the cells between A(1) and A(n+1) with integer values (by repeating them if needed) ?

Expected result:

    +-----------+   +-----------+
    |     |  A  |   |     |  A  |
    |  1  | 444 |   |  1  | 444 |
    |  2  |     |   |  2  | 527 |
    |  3  |     |   |  3  | 610 |
    |  4  |     |   |  4  | 693 |
    |  5  |     |   |  5  | 776 |
    |  6  |     |   |  6  | 859 |
    |  7  | 938 |   |  7  | 938 |
    +-----------+   +-----------+

Another example:

    +-----------+   +-----------+
    |     |  A  |   |     |  A  |
    |  1  |  5  |   |  1  |  5  |
    |  2  |     |   |  2  |  5  |
    |  3  |     |   |  3  |  6  |
    |  4  |     |   |  4  |  6  |
    |  5  |     |   |  5  |  7  |
    |  6  |     |   |  6  |  7  |
    |  7  |  8  |   |  7  |  8  |
    +-----------+   +-----------+

Thank you.

Solution

If you want an even distribution, with each cell rounded up to the next integer, enter the following formula into cell A2:

=ROUNDUP(A$1+(ROW()-ROW(A$1))*(A$7-A$1)/(ROW(A$7)-ROW(A$1)))

replacing n with the row of the last value (in your example 7). Then copy the cell down up to An (not An+1).

---

If you want the same values as in your example, take this formula:

=ROUNDUP(A1+(A$n-A$1)/(ROW(A$n)-ROW(A$1)))

But this suffers from a severe rounding bias: This adds a rounded up value of 83 from row to row, but for the last step there is only 79 remaining, making it significantly smaller. In the upper formula, the rounded difference changes between 82 and 83 from row to row, resulting in a more even spread.

Comments

[Admin]

Copy =[previous cell]+83 to all open cells?