arrays

[edmac]

how do i find the first cell containing a number in a given row or column and
return its value and location? how about the last cell that contains a number
and return that number and location?

 

[Peo Sjoblom]

1a =INDEX(C1:C100,MATCH(TRUE,ISNUMBER(C1:C100),0))

1b =CELL("address",INDEX(C1:C100,MATCH(TRUE,ISNUMBER(C1:C100),0)))

2a =INDEX(C1:C100,MAX((ISNUMBER(C1:C100)*ROW(C1:C100))))

2b =CELL("address",INDEX(C1:C100,MAX((ISNUMBER(C1:C100)*ROW(C1:C100)))))

all entered with ctrl + shift & enter
to retrieve the last number you can also use the simpler

=LOOKUP(9.99999999999999E+307,C:C)

entered normally




--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)