Hi Ted
One way would be an array formula (Use Control+Shift+Enter rather than
Enter, and Excel will insert the curly braces { }. Do not enter the { ]
braces yourself)
In cell F2
{=INDEX(B2:E2, MATCH(TRUE,
(B2:E2<""),0))-INDEX(2:,0,MAX((B2:E2<"")*(COLUMN(B2:E2))))}
Copy down as necessary
--
Regards

Roger Govier


"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