Creating a count variable for interpolation

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

The 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

=$C$2+row($A1)*((C$11-C$2)/($A$11-$A$2))

Use a dollar sign to fix the part of the range reference you want to not
change.

$A1 fixes the column

A$1 fixes the row

$A$1 fixes the row and column

--
Regards,
Tom Ogilvy


<mshall2@gmail.com> wrote in message
news:1138586342.256326.21760@g47g2000cwa.googlegroups.com...
> 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
>