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?