Subtracting first and last values in a row to find net differe
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
|