referencing last non-empty cell...

I have the results of a formula listed in a column so that the cells either
contain a value or a #N/A like this:

0.019698
-0.12854
#N/A
0.716724
#N/A
#N/A
#N/A
#N/A
-0.61727
#N/A
#N/A
#N/A
0.709301
#N/A
#N/A
#N/A
#N/A


Say, for example, I wanted to subtract an earlier value from the next value,
how can this be done via a cell formula, or if not, via a VB routine? Say,
..709301 then subtracting the prior value of -.61727 bypassing however many
#N/As occur in the interim period cells.

My thanks in advance for any help or directions on this matter.

Cordially, Brad

i = -1
do while iserror(activecell.offset(i,0))
i = i - 1
if activecell.row + i < 2 then exit sub
Loop
Activecell.Value = ActiveCell.Value + activeCell.Offset(i,0).Value

--
Regards,
Tom Ogilvy

"Brad" <lreid@flash.net> wrote in message
news:%23KNiThqwFHA.1456@TK2MSFTNGP11.phx.gbl...
> I have the results of a formula listed in a column so that the cells
either
> contain a value or a #N/A like this:
>
> 0.019698
> -0.12854
> #N/A
> 0.716724
> #N/A
> #N/A
> #N/A
> #N/A
> -0.61727
> #N/A
> #N/A
> #N/A
> 0.709301
> #N/A
> #N/A
> #N/A
> #N/A
>
>
> Say, for example, I wanted to subtract an earlier value from the next
value,
> how can this be done via a cell formula, or if not, via a VB routine? Say,
> .709301 then subtracting the prior value of -.61727 bypassing however many
> #N/As occur in the interim period cells.
>
> My thanks in advance for any help or directions on this matter.
>
> Cordially, Brad
>
>

Brad,

Let's say that your numbers and NA's are in column A, starting in row 2. In Cell B2, array enter
(enter using Ctrl-Shift-Enter) the formula

=IF(ISERROR(A2),"",A2-INDEX(A:A,MAX(NOT(ISERROR(($A1:A$2)))*ROW($A1:A$2))))

and copy down to match the values in column A.

HTH,
Bernie
MS Excel MVP


"Brad" <lreid@flash.net> wrote in message news:%23KNiThqwFHA.1456@TK2MSFTNGP11.phx.gbl...
>I have the results of a formula listed in a column so that the cells either contain a value or a
>#N/A like this:
>
> 0.019698
> -0.12854
> #N/A
> 0.716724
> #N/A
> #N/A
> #N/A
> #N/A
> -0.61727
> #N/A
> #N/A
> #N/A
> 0.709301
> #N/A
> #N/A
> #N/A
> #N/A
>
>
> Say, for example, I wanted to subtract an earlier value from the next value, how can this be done
> via a cell formula, or if not, via a VB routine? Say, .709301 then subtracting the prior value
> of -.61727 bypassing however many #N/As occur in the interim period cells.
>
> My thanks in advance for any help or directions on this matter.
>
> Cordially, Brad
>