Creating a Artificial Count Variable...for Interpolating

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

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

<[email protected]> wrote:
> .. 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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---