subtracting based on available values

[Guest]

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

 

[Max]

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

 

[Max]

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

 

[Roger Govier]

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

 

[Guest]

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

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

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

 

[Max]

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

--