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

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

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!

"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

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

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)))

--
Rick (MVP - Excel)


"Tropikat" wrote in message
...
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

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

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

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

Try this...

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

--
Rick (MVP - Excel)


"Tropikat" wrote in message
...
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

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

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