Calculation

I have the following:
col A
row 1 15,000
row 2 12,500
row 3 10,000
row 4
row 5 _______
Attrition= 5,000 - The formula in this cell would be the last row with
a figure (in this example row 3) subtracted from the figure in row 1. If row
5 had a figure then the formula would subtracted row 5 from row 1. Does
anyone know a formula for this? Thanks in advance.

=a1-offset(a1,count(a1:a5)-1,0)



"Mike" <[email protected]> wrote in message
news:[email protected]...
>I have the following:
> col A
> row 1 15,000
> row 2 12,500
> row 3 10,000
> row 4
> row 5 _______
> Attrition= 5,000 - The formula in this cell would be the last row
> with
> a figure (in this example row 3) subtracted from the figure in row 1. If
> row
> 5 had a figure then the formula would subtracted row 5 from row 1. Does
> anyone know a formula for this? Thanks in advance.

If A6 is the attrition cell and

[1] if A1 has always a figure (never empty)...

=A1-LOOKUP(9.99999999999999E+307,A1:OFFSET(A6,-1,0,1,1))

[2] otherwise...

=INDEX(A:A,MATCH(TRUE,ISNUMBER(A1:OFFSET(A6,-1,0,1,1)),0))-LOOKUP(9.99999999999999E+307,A1:OFFSET(A6,-1,0,1,1))

which you need to confirm with control+shift+enter instead of just with
enter.

If a non-A cell, say C1, is the attrition cell:

=OFFSET(A1,MATCH(TRUE,ISNUMBER(A1:INDEX(A:A,MATCH(9.99999999999999E+307,A:A))),0)-1,0,1,1)-LOOKUP(9.99999999999999E+307,A:A)

which must be confirmed with control+shift+enter instead of just with enter.


Mike wrote:
> I have the following:
> col A
> row 1 15,000
> row 2 12,500
> row 3 10,000
> row 4
> row 5 _______
> Attrition= 5,000 - The formula in this cell would be the last row with
> a figure (in this example row 3) subtracted from the figure in row 1. If row
> 5 had a figure then the formula would subtracted row 5 from row 1. Does
> anyone know a formula for this? Thanks in advance.