Searching for #N/A
To find the relative position of the last instance of #N/A:
=LOOKUP(2,1/ISNA(A2:E2),COLUMN(A2:E2)-MIN(COLUMN(A2:E2))+1)
To find the relative position of the first number:
Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):
=MATCH(1,--ISNUMBER(A2:E2),0)
Biff
wrote in message
oups.com...
I'm trying to create a formula that will tell me where the last
occurance of #N/A occurs in a table. If I have,
1 2 3 4 5
#N/A #N/A #N/A 2% 8%
I want a formula to tell me that the last occurance of #N/A is in
column 3, or that the first occurance of a real number is in column 4.
You can assume that the sets of #N/A will be contiguous.
Thank you.
-bgetson
|