One way ..

Assuming this table is in A1:E5

2005 2004 2003 2002
bob 7 5 1
dave 6 2
chris 5 3 3
greg 9 8 4

Put in F2 and array-enter, i.e. press CTRL+SHIFT+ENTER:

=INDEX(B2:E2,MATCH(MAX((B$1:E$1)*(B2:E2<0)),(B$1: E$1)*(B2:E2<0),0)) -
INDEX(B2:E2,MATCH(MIN(IF((B$1:E$1)*(B2:E2<0)<0,( B$1:E$1)*(B2:E2<0))),(B$1
:E$1)*(B2:E2<0),0))

Copy F2 down to F5
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Ted Metro" wrote in message
...
This has to be pretty easy, and I did it with a huge IF statement, but
there
has to be a simpler way.

I have data like this
2005 2004 2003 2002
bob 7 5 1
dave 6 2
chris 5 3 3
greg 9 8 4

There are some blanks in the table where there is simply no value
I want a formula that will subtract the oldest available from the newest
available.

So for Bob it will subtract 7-1, for Dave it will be 6-2, for Chris it
will
be 5-3, and for Greg it will be 9-4.

So it looks for the data in the furthest left cell in the range and the
subtracts the value in the cell that is furthest right.

Is this an easy fix?

Ted