Counting

[Guest]

If I had columns with data, and up to a certain point and after a certain point (which is dynamic across columns) the data points were #N/A but then became numbers. like
#N/
#N/
10
10
#N/

How can I count the number of #N/A BEFORE THE NUMBERS START, ignoring the amount after the numbers end (which varies across columns and the number of #N/As are dynamic after the numbers stop also)

any help would be appreciate

 

[Jason Morin]

Col. A for example:

=MIN(IF(ISNUMBER(OFFSET(A1,,,COUNTA(A:A))),ROW(OFFSET
(A1,,,COUNTA(A:A)))))-1

Array-entered (press ctrl/shift/enter).

HTH
Jason
Atlanta, GA

-----Original Message-----
If I had columns with data, and up to a certain point and

after a certain point (which is dynamic across columns)
the data points were #N/A but then became numbers. like:

#N/A
#N/A
100
102
#N/A

How can I count the number of #N/A BEFORE THE NUMBERS

START, ignoring the amount after the numbers end (which
varies across columns and the number of #N/As are dynamic
after the numbers stop also)?

 

[Frank Kabel]

Hi
try the following array entered formula (assumption: your range is
A1:A100):
=MIN(IF(ISNUMBER(OFFSET($A$1:$A$100,MIN(IF(ISNA(A1:A100),ROW(A1:A100)))
,0)),ROW(OFFSET($A$1:$A$100,MIN(IF(ISNA(A1:A100),ROW(A1:A100))),0))))-M
IN(IF(ISNA(A1:A100),ROW(A1:A100)))

 

[Guest]

Couldnt get that to give correct answe

sorry, maybe it is impossbile

Thanks

 

[Jason Morin]

What value or error message are you getting? Are you
remembering to press ctrl/shift/enter after inserting the
formula?

Jason

 

[Frank Kabel]

Hi
what problem did you exactly encounter. Tested it on some test data
(starting in row 1, column A) and it works (if I understood you
correctly).