subtracting based on available values
 

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

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

Just a slight tweak to formula ..
(but think the earlier one would also work as well)

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

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

Copy F2 down to F5
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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

Thanks Max and Roger!

Max your solution worked perfectly!! I couldn't get Roger's to work
however. I assume that in the second half of the formula the array is b2:f2
again (I think your ":2" is just a small typo in your e-mail back to me).

Chris

"Roger Govier" wrote:

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

You're welcome, Chris !

Roger's formula as posted had only a slight typo.
( It's much neater !)

.. I assume that in the second half of the formula the array is b2:f2
again

Think it should be 2:2

Try array-entering in F2 as:

=INDEX(B2:E2,MATCH(TRUE,
(B2:E2<""),0))-INDEX(2:2,0,MAX((B2:E2<"")*(COLUMN(B2:E2))))

Then just copy F2 down
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Ted Metro" wrote in message
...
Thanks Max and Roger!

Max your solution worked perfectly!! I couldn't get Roger's to work
however. I assume that in the second half of the formula the array is
b2:f2
again (I think your ":2" is just a small typo in your e-mail back to me).

Chris