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" wrote in message
...
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.