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

"Tropikat" wrote:

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" wrote:

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(A 1:E1),0))-LOOKUP(2,1/(A1:E1),A1:E1),"")

Once again ARRAY entered.

Mike

"Tropikat" wrote:

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" wrote:

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" wrote:

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

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

"Mike H" wrote:

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" wrote:

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