Subtracting first and last values in a row to find net difference

[Tropikat]

What formula could I use to automatically locate the first and last listed
values in a row and subtract those two numbers to find the net difference?

A B C D E

1 150 145 147 140

2 240 235 220

In example Row 1 I want to subtract A1 from E1
In example Row 2 I want to subtract B2 from D2

 

[Mike H]

Hi,

I'm not sure which way around you want this but you can see the minus sign
in the middle of the formula simply swap the sides if I've got it wrong


For last-first in row 1
=LOOKUP(2,1/(1:1),1:1)-INDEX(1:1,MATCH(TRUE,ISNUMBER(1:1),0))

For last - first in the range you gave
=LOOKUP(2,1/(A1:E1),A1:E1)-INDEX(A1:E1,MATCH(TRUE,ISNUMBER(A1:E1),0))

Both of these are array formula and must be entered with CTRL+Shift +Enter
and NOT just enter. When entered drag down

Mike

 

[Tropikat]

This gives me the correct number, but in the reverse value. So I'm getting
-14 when it should be 14. Tried swapping the formula sides, but I'm getting
an error value. What am I doing wrong? Thanks for all your help!

 

[Tropikat]

Just realized I'd made an error in my first question.

I needed to subtract E1 from A1 and D2 from B2.... Sorry!

 

[Rick Rothstein]

Try this modification of Mike's formula (still array-entered)...

=ABS((LOOKUP(2,1/(1:1),1:1))-INDEX(1:1,MATCH(TRUE,ISNUMBER(1:1),0)))

 

[Mike H]

for a1-e1

=INDEX(A1:E1,MATCH(TRUE,ISNUMBER(A1:E1),0))-LOOKUP(2,1/(A1:E1),A1:E1)

Remember it's an array see previous instruction regarding CTRL+Shift+Enter

Put simply
=LOOKUP(2,1/(A1:E1),A1:E1)
extracts the last number

=INDEX(A1:E1,MATCH(TRUE,ISNUMBER(A1:E1),0))
Extracts the first

because the 2nd formula is an array the whole thing has to be array entered.

Mike

 

[Tropikat]

Brilliant! I'm getting the correct results, with the exception of the #N/A
error. Any way to modify the formula to get rid of these error values? I
also get this error when I SUM the results column.

 

[Mike H]

hi,

the only way I can replicate #N/A is if there are no numbers in the range so
try this

=IF(COUNT(A1:E1),INDEX(A1:E1,MATCH(TRUE,ISNUMBER(A1:E1),0))-LOOKUP(2,1/(A1:E1),A1:E1),"")

Once again ARRAY entered.

Mike

 

[Rick Rothstein]

Try this...

=IF(COUNT(A1:E1)<2,"",INDEX(A1:E1,MATCH(TRUE,ISNUMBER(A1:E1),0))-LOOKUP(2,1/(A1:E1),A1:E1))

 

[Tropikat]

It works...it works! (Does a little jig around the room) Can't thank you
enough. This would have taken me a lifetime to figure out without your help.

 

[Mike H]

I knew we'd get there in the end. Your welcome

It works...it works! (Does a little jig around the room) Can't thank you
enough. This would have taken me a lifetime to figure out without your help.