Interpolate data points in table

[Guest]

I have 2 columns (A:B). A is numbered 1-17. B then has % figures in some of
the rows. I want to interpolate the gaps between these figures (see example
below).

At present I do this manually using the following formula:
((a-b)/x+c)
Where a is the 1st known amount above, b is the first known amount below, c
is the value of the row below, and x is the number of steps between a and b
(e.g. row 1 to 4 is 3 steps).
Row 1 will always have a figure in it, but the rest are then random and
there is no need to interpolate after the last figure (which may be at any
point from row 1-17)

I would like to be able to do this automatically, where the known amounts
are input, and then the other figures calculate - the output would preferably
be in a new column.

Any ideas?

E.g:
Inputs Desired Result
1 13.40% 13.40%
2 10.90%
3 8.40% 8.40%
4 6.83%
5 5.27%
6 3.70% 3.70%
7 2.93%
8 2.17%
9 1.40% 1.40%
10
11
12
13
14
15
16
17

 

[Lori]

With the given layout a quick way to fill the gaps in column B is to
hold down ctrl and select the ranges containing end points so that the
selections overlap by one then use the series command.

i.e. select (B1:B3,B3:B6,B1:B9) then edit>fill>series>trend>OK

Another method is to set each blank cell equal to the midpoint of the
neighbouring cells. For this make sure tools > options > iteration is
selected with e.g. max change 1e-15.

i.e. choose edit>goto>special>blanks and with B2 active type =(B1+B3)/
2 [ctrl+enter]

Copy column B to C initially if you need the output in a new column

 

[Lori]

If you need a formula to fill down column C try array-entered (ctrl
+shift+enter):

=PERCENTILE($B$1:$B$9,PERCENTRANK(IF($B$1:$B$9,-$A$1:$A$9),-A1,308))

 

[Guest]

Thanks this is great.
With the help of another question elsewhere I have been able to build a
macro to automate this.

Thanks

With the given layout a quick way to fill the gaps in column B is to
hold down ctrl and select the ranges containing end points so that the
selections overlap by one then use the series command.

i.e. select (B1:B3,B3:B6,B1:B9) then edit>fill>series>trend>OK

Another method is to set each blank cell equal to the midpoint of the
neighbouring cells. For this make sure tools > options > iteration is
selected with e.g. max change 1e-15.

i.e. choose edit>goto>special>blanks and with B2 active type =(B1+B3)/
2 [ctrl+enter]

Copy column B to C initially if you need the output in a new column

I have 2 columns (A:B). A is numbered 1-17. B then has % figures in some of
the rows. I want to interpolate the gaps between these figures (see example
below).

At present I do this manually using the following formula:
((a-b)/x+c)
Where a is the 1st known amount above, b is the first known amount below, c
is the value of the row below, and x is the number of steps between a and b
(e.g. row 1 to 4 is 3 steps).
Row 1 will always have a figure in it, but the rest are then random and
there is no need to interpolate after the last figure (which may be at any
point from row 1-17)

I would like to be able to do this automatically, where the known amounts
are input, and then the other figures calculate - the output would preferably
be in a new column.

Any ideas?

E.g:
Inputs Desired Result
1 13.40% 13.40%
2 10.90%
3 8.40% 8.40%
4 6.83%
5 5.27%
6 3.70% 3.70%
7 2.93%
8 2.17%
9 1.40% 1.40%
10
11
12
13
14
15
16
17