Guys,

I have some time series data but with gaps in between. Is there a way to get
Excel to fill in the blanks between two numbers on a straight line basis?

Eg: If I have in A1 the number 5 and in A4 the number 11, is there a way to
automatically fill A2 with 7 and A3 with 9?

Thanks in advance.

Hi,

the auto-fill (fill handle) can't do this. If you knew that the cells would
always have two blank cells between them you can use a formula.

If 5 is in A1 and 11 in A4 the formula in A2 would be

=A1+(A$4-A$1)/3

And copy this down.

If the number of blanks can vary, say your first number is in D1 and the
second in D5 then a more general approach would be

=D1+(D$5-D$1)/(ROWS($D$1:$D$5)-1)

enter this formula in D2 and fill it down.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"sticky" wrote:

Guys,

I have some time series data but with gaps in between. Is there a way to get
Excel to fill in the blanks between two numbers on a straight line basis?

Eg: If I have in A1 the number 5 and in A4 the number 11, is there a way to
automatically fill A2 with 7 and A3 with 9?

Thanks in advance.