Subtraction by row

[Praetorian Prefect]

I have 8 cells, A1:H1, two of the cells will have values, the rest
will be blank. I am able to get the last cell that has a value using
=LOOKUP(10^99,A1:H1), but I am not able to subtract the value left of
the last cell that contains a value. Example:

C1 has a value of 5, D1 has value of 4, the rest are blanks. I1
contains the difference between the cells. Using LOOKUP(10^99,a1:h1)
will return the value 4 where the value of C1, 5, will be subtracted
from. Tried using MAX, MIN, SMALL, LARGE.

A1 B1 C1 D1 E1 F1 G1 H1 I1
5 4 -1

Hope its clear. Thank you in advance.

 

[Tom Ogilvy]

=LOOKUP(10^99,A1:H1)-
INDEX(A1:H1,1,SMALL(IF(ISNUMBER(A1:H1),COLUMN(A1:H1),100),1))

Entered with Ctrl+Shift+Enter rather than just enter since this is an array
formula.

 

[Praetorian Prefect]

=LOOKUP(10^99,A1:H1)-
INDEX(A1:H1,1,SMALL(IF(ISNUMBER(A1:H1),COLUMN(A1:H1),100),1))

Entered with Ctrl+Shift+Enter rather than just enter since this is an array
formula.

--
Regards,
Tom Ogilvy








- Show quoted text -

Thank you. It worked perfectly.

 

[Praetorian Prefect]

=LOOKUP(10^99,A1:H1)-
INDEX(A1:H1,1,SMALL(IF(ISNUMBER(A1:H1),COLUMN(A1:H1),100),1))

Entered with Ctrl+Shift+Enter rather than just enter since this is an array
formula.

--
Regards,
Tom Ogilvy








- Show quoted text -

What is the purpose of the value 100?

 

[Praetorian Prefect]

=LOOKUP(10^99,A1:H1)-
INDEX(A1:H1,1,SMALL(IF(ISNUMBER(A1:H1),COLUMN(A1:H1),100),1))

Entered with Ctrl+Shift+Enter rather than just enter since this is an array
formula.

--
Regards,
Tom Ogilvy








- Show quoted text -

I'm curious, if B1 also contains a value, 4, will D1 still subtract C1?

 

[Tom Ogilvy]

the stated situation was:

I have 8 cells, A1:H1, two of the cells will have values, the rest
will be blank.

So, it would subtract the first value found in the range from the last value
found in the range.

 

[Tom Ogilvy]

You could leave the 100 out

=LOOKUP(10^99,A1:H1)-INDEX(A1:H1,1,SMALL(IF(ISNUMBER(A1:H1),COLUMN(A1:H1)),1))