Subtraction by row

  • Thread starter Thread starter Praetorian Prefect
  • Start date Start date
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.
 
=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.
 
=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 -
Click to expand...

Thank you. It worked perfectly.
 
=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 -
Click to expand...

What is the purpose of the value 100?
 
=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 -
Click to expand...

I'm curious, if B1 also contains a value, 4, will D1 still subtract C1?
 
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.
 
You could leave the 100 out

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