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

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 editfillseriestrendOK

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 editgotospecialblanks 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


On 24 May, 12:26, pjd33 wrote:
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

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))

On 24 May, 12:26, pjd33 wrote:
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

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

Thanks

"Lori" wrote:

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 editfillseriestrendOK

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 editgotospecialblanks 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


On 24 May, 12:26, pjd33 wrote:
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