Creating a Artificial Count Variable...for Interpolating

[mshall2]

Hi,

I have an enormous worksheet that I'm working with. Here' s a very
small part of it (immigration data):
A B C
D
1 lag_year total_stock afghan_stock
argentina_stock
2 1996 21631601 26988 97422
3 1997 23526859
4 1998 25422116
5 1999 27317374
6 2000 29212631
7 2001 31107889
8 2002 31916574
9 2003 32725260
10 2004 33533945
11 2005 34279584 48093 185144

As you can see I have data from 1996 and 2005 and want to interpolate
the values in between. Now this matrix is 573x200, so creating a new
column for the interpol() func for every country (and state) is a
little out of the question. So, I figure I can write a function like:
"C3=C2+1*((C11-C2)/(A11-A2))." This works fine, but when I drag, the
only thing I want to change is the '1', to a 2. So,
C4=C2+2*((C11-C2)/(A11-A2)), and so on. Then I want to be able to drag
across columns so I get "D3=C2+1*((D11-D2)/(A11-A2))" and so on. I've
tried inserted a '!' like you would in SAS, but it doesn't work. I'm
not real familiar with VBA, but I have a feeling that's the way to go.

I thank you greatly for any help you can provide.
Graciously,

Matt Hall
Penn State

 

[mshall2]

That data didn't turn out well. The first column (A) is lag_year, the
second (B)is total_stock, the third (C) is afghan_stock, and the fourth
(D) is argentina_stock.
Thanks

 

[Max]

.. So, I figure I can write a function like:
"C3=C2+1*((C11-C2)/(A11-A2))." This works fine, but when I drag,
the only thing I want to change is the '1', to a 2. So,
C4=C2+2*((C11-C2)/(A11-A2)), and so on.
Then I want to be able to drag across columns so I get
"D3=C2+1*((D11-D2)/(A11-A2))" and so on.

Try this in the starting cell, C3:
=$C$2+ROW(A1)*((C$11-C$2)/($A$11-$A$2))
then copy across/down

The formula will increment/change in the desired manner