Subtraction by row

P

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.
 
T

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.
 
P

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.
 
P

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?
 
P

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?
 
T

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.
 
T

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top