Interpolation

[Paul Hyett]

If I have two values in a column separated by a certain number of rows,
e.g.

1000
x
x
x
x
500

.... and I wanted to fill each cell in between by reducing by (in this
example) 1/5th of the difference, to get an end result of :

1000
900
800
700
600
500

Is there a way of automating this for a variable number of rows between
the start & end numbers?

I can obviously do it manually where the gap is a few rows, but
otherwise...

[I hope I've explained this well enough?]

 

[Andy Pope]

Hi,

Assuming your example is in the range A1:A6 then this in A2 and copied down
to A5 will provide values to interpolate between start and finsh values.

=A1-($A$1-$A$6)/(ROWS($A$1:$A$6)-1)

Cheers
Andy

 

[Niek Otten]

Let's say the 1000 is in A1 and the 500 is in A6. Put the number of lines,
including the last one, in B1; in this example 5.
In A2:A5, use this formula:

=A1-($A$1-OFFSET($A$1,$B$1,))/$B$1

 

[Bernd P]

Hello Paul,

You can interpolate with my UDF Interp:
http://www.sulprobil.com/html/interpolate.html

Just take the row numbers as vX values...

Regards,
Bernd

 

[Paul Hyett]

Hello Paul,

You can interpolate with my UDF Interp:
http://www.sulprobil.com/html/interpolate.html

Just take the row numbers as vX values...

Thank you for this, but having never used VBA, I think I'd better stick
to solutions involving formulas.

 

[Paul Hyett]

Let's say the 1000 is in A1 and the 500 is in A6. Put the number of lines,
including the last one, in B1; in this example 5.
In A2:A5, use this formula:

=A1-($A$1-OFFSET($A$1,$B$1,))/$B$1

Thank you.

This one also partially does what I want, but with the absolute cell
references it has somewhat of the same issues as Andy's solution.

 

[Paul Hyett]

Hi,

Assuming your example is in the range A1:A6 then this in A2 and copied
down to A5 will provide values to interpolate between start and finsh
values.

=A1-($A$1-$A$6)/(ROWS($A$1:$A$6)-1)

Thank you.

Yes, this works for the example I gave, but I have a number of other
gaps to fill of varying length, and the absolute references mean I can't
just copy/paste this. I would have to alter the formula for each
different length gap, which is what I wanted to avoid.

 

[Niek Otten]

No, just the starting cell is fixed. You just change the number in B1 and it
will take a new range.
Of course that could be made automatic too, if it is imortant.

 

[Harlan Grove]

If I have two values in a column separated by a certain number of rows, ....
1000
x
x
x
x
500

... and I wanted to fill each cell in between by reducing by (in this
example) 1/5th of the difference, to get an end result of :

1000
900
800
700
600
500

Is there a way of automating this for a variable number of rows between
the start & end numbers?

....

If you had 1000 in cell C3 and 500 in cell C8, select C3:C8, run the
menu command Edit > Fill > Series, and click the OK in the Series
dialog. Formulas aren't necessary.

 

[Lori]

If you need to fill more than one range the Edit > Series method also works
on multiple ranges by ctrl+clicking e.g. (a1:a6,a6:a9) where the ranges can
overlap.

Or less manually... Select Tools > Options General: R1C1 notation >
Calculation: Iteration, Max change=0. Now choose Edit > Replace blank with:
=average(r[-1]c,r[1]c) with the whole range selected. Paste the values and
restore settings after.

 

[Paul Hyett]

If you had 1000 in cell C3 and 500 in cell C8, select C3:C8, run the
menu command Edit > Fill > Series, and click the OK in the Series
dialog. Formulas aren't necessary.

Thank you - that's just what I was looking for!

Yet another Excel option I had no idea existed - it was so much easier
to find them when we had printed manuals to peruse...

 

[Lori]

i meant to add that you need to check the trend box if using the
Edit>Fill>Series command with a multiple range.

i also just noticed a peculiarity with the Edit>Fill options which could be
useful for filling blank values from above. Selecting A2:A5,A6:A8 and
pressing Ctrl+D fills down the same value from A2 but with A2:A4,A6:A8,B1
selected each range is filled separately. i presume this behaviour is
consistent across versions?

If you need to fill more than one range the Edit > Series method also works
on multiple ranges by ctrl+clicking e.g. (a1:a6,a6:a9) where the ranges can
overlap.

Or less manually... Select Tools > Options General: R1C1 notation >
Calculation: Iteration, Max change=0. Now choose Edit > Replace blank with:
=average(r[-1]c,r[1]c) with the whole range selected. Paste the values and
restore settings after.

If I have two values in a column separated by a certain number of rows,
e.g.

1000
x
x
x
x
500

.... and I wanted to fill each cell in between by reducing by (in this
example) 1/5th of the difference, to get an end result of :

1000
900
800
700
600
500

Is there a way of automating this for a variable number of rows between
the start & end numbers?

I can obviously do it manually where the gap is a few rows, but
otherwise...

[I hope I've explained this well enough?]