Automatically use the newest number input in the formulas
 

I have to update this YTD calculations every month. I get an input to put in
for Nov, and the YTD formula will take the Nov number and calculate the
answer. When i get the Dec input, i have to change the formula to refer to
the Dec input instead of NOV input. this gets really annoying when i have 100
tables.

Super Fund

Return (%)
100.00
1/31/2006 100.00
2/28/2006 100.00
3/31/2006 100.00
4/30/2006 0.20 100.20
5/31/2006 0.40 100.60
6/30/2006 0.61 101.21
7/31/2006 0.90 102.13
8/31/2006 0.56 102.70
9/30/2006 0.65 103.36
10/31/2006 1.03 104.43
11/30/2006 1.24 105.72
12/31/2006 0.66 106.42 (N18)
1/31/2007 0.91 107.39
2/28/2007 0.73 108.17
3/31/2007 0.79 109.03
4/30/2007 0.95 110.06
5/31/2007 1.94 112.20
6/30/2007 -0.25 111.92
7/31/2007 -1.54 110.20
8/31/2007 -0.05 110.14
9/30/2007 0.79 111.01
10/31/2007 0.14 111.17
11/30/2007 -1.65 109.33
12/31/2007 -1.35 107.86 (N30)
1/31/2008
TOTAL 7.86 107.86

YTD: =(((N30*0.01)/(N18*0.01))-1)*100

Automatically use the newest number input in the formulas
 

In your formula, replace N30 with:
LOOKUP(9.99999999999999E+307,N:N)

This expression will pick up the last numeric value in column N
--
Gary''s Student - gsnu200765


"JessieB" wrote:

I have to update this YTD calculations every month. I get an input to put in
for Nov, and the YTD formula will take the Nov number and calculate the
answer. When i get the Dec input, i have to change the formula to refer to
the Dec input instead of NOV input. this gets really annoying when i have 100
tables.

Super Fund

Return (%)
100.00
1/31/2006 100.00
2/28/2006 100.00
3/31/2006 100.00
4/30/2006 0.20 100.20
5/31/2006 0.40 100.60
6/30/2006 0.61 101.21
7/31/2006 0.90 102.13
8/31/2006 0.56 102.70
9/30/2006 0.65 103.36
10/31/2006 1.03 104.43
11/30/2006 1.24 105.72
12/31/2006 0.66 106.42 (N18)
1/31/2007 0.91 107.39
2/28/2007 0.73 108.17
3/31/2007 0.79 109.03
4/30/2007 0.95 110.06
5/31/2007 1.94 112.20
6/30/2007 -0.25 111.92
7/31/2007 -1.54 110.20
8/31/2007 -0.05 110.14
9/30/2007 0.79 111.01
10/31/2007 0.14 111.17
11/30/2007 -1.65 109.33
12/31/2007 -1.35 107.86 (N30)
1/31/2008
TOTAL 7.86 107.86

YTD: =(((N30*0.01)/(N18*0.01))-1)*100