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

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

--
Regards,
Tom Ogilvy


"Praetorian Prefect" wrote:

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.

On Apr 11, 8:44 am, Tom Ogilvy
wrote:
=LOOKUP(10^99,A1:H1)-
INDEX(A1:H1,1,SMALL(IF(ISNUMBER(A1:H1),COLUMN(A1:H 1),100),1))

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

--
Regards,
Tom Ogilvy



"Praetorian Prefect" wrote:
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.- Hide quoted text -

- Show quoted text -

Thank you. It worked perfectly.

On Apr 11, 8:44 am, Tom Ogilvy
wrote:
=LOOKUP(10^99,A1:H1)-
INDEX(A1:H1,1,SMALL(IF(ISNUMBER(A1:H1),COLUMN(A1:H 1),100),1))

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

--
Regards,
Tom Ogilvy



"Praetorian Prefect" wrote:
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.- Hide quoted text -

- Show quoted text -

What is the purpose of the value 100?

On Apr 11, 8:44 am, Tom Ogilvy
wrote:
=LOOKUP(10^99,A1:H1)-
INDEX(A1:H1,1,SMALL(IF(ISNUMBER(A1:H1),COLUMN(A1:H 1),100),1))

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

--
Regards,
Tom Ogilvy



"Praetorian Prefect" wrote:
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.- Hide quoted text -

- Show quoted text -

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.

--
Regards,
Tom Ogilvy


"Praetorian Prefect" wrote:

On Apr 11, 8:44 am, Tom Ogilvy
wrote:
=LOOKUP(10^99,A1:H1)-
INDEX(A1:H1,1,SMALL(IF(ISNUMBER(A1:H1),COLUMN(A1:H 1),100),1))

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

--
Regards,
Tom Ogilvy



"Praetorian Prefect" wrote:
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.- Hide quoted text -

- Show quoted text -

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

You could leave the 100 out

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

--
Regards,
Tom Ogilvy


"Praetorian Prefect" wrote:

On Apr 11, 8:44 am, Tom Ogilvy
wrote:
=LOOKUP(10^99,A1:H1)-
INDEX(A1:H1,1,SMALL(IF(ISNUMBER(A1:H1),COLUMN(A1:H 1),100),1))

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

--
Regards,
Tom Ogilvy



"Praetorian Prefect" wrote:
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.- Hide quoted text -

- Show quoted text -

What is the purpose of the value 100?