subtracting based on available values

T

Ted Metro

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
 
M

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
Click to expand...

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
 
M

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:
Click to expand...

=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
 
R

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
 
T

Ted Metro

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 said:
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 said:
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
Click to expand...
Click to expand...
 
M

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
Click to expand...

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
 
Top