finding the no. of rows in a COL filled with numbers, zeros and bl

Z

z.entropic

Attempting to autorange the chart, I've tried to use Ozgrid's solutions with
named ranges, e.g.:
===============
Expand Down to The Last Numeric Entry
In the Refers to box type: =OFFSET($A$1,0,0,MATCH(1E+306,$A:$A,1),1)

4:Expand Down to The Last Text Entry
In the Refers to box type: =OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1)
================

Somehow, these formulas choke up with my mix of numbers, text strings and
blanks. Unfortunately, the column can have either as the last item. COUNT,
COUNTA and COUNTBLANK didn't help to resolve the issue. Is there a better
way to count the number of rows with such a mix of entries?

z.entropic
 
B

bj

I am not sure what you are trying to do.
Are you trying to find the number of rows that have something in them.
or are you trying to find the location of the last row with something in it?
is this for a defined area of a larger spreadsheet, or is it for the entire
spread sheet.
 
Z

z.entropic

I'm trying to find the number of rows with mixed data and blanks between them
in the entire wprksheet. The number of rows is the same in each column.

z.entropic
 
D

Domenic

Try something like this...

=OFFSET($A$2,0,0,MATCH(2,1/($A$2:$A$65536<>"")))

Note that you cannot use a whole column reference for the MATCH function
in this case.

Hope this helps!
 
A

Aladin Akyurek

z.entropic said:
Attempting to autorange the chart, I've tried to use Ozgrid's solutions with
named ranges, e.g.:
===============
Expand Down to The Last Numeric Entry
In the Refers to box type: =OFFSET($A$1,0,0,MATCH(1E+306,$A:$A,1),1)

4:Expand Down to The Last Text Entry
In the Refers to box type: =OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1)
================

Somehow, these formulas choke up with my mix of numbers, text strings and
blanks. Unfortunately, the column can have either as the last item. COUNT,
COUNTA and COUNTBLANK didn't help to resolve the issue. Is there a better
way to count the number of rows with such a mix of entries?

z.entropic
Click to expand...

E2;

=MATCH(9.99999999999999E+307,A:A)

E3:

=MATCH(REPT("z",255),A:A)

E4:

=IF(COUNT(E2:E3)=2,MAX(E2:E3),SUMIF(E2:E3,"<>#N/A"))

The following would define a dynamic range in A on Sheet1:

=Sheet1!$A$1:INDEX(Sheet1!$A:$A,E4)
 
D

Domenic

I like this one. Definitely more efficient than the one I offered.
I'll have to remember it. :)
 
D

Don Guillett

You may even like this better. Add 9's and z's if you like
=MAX(MATCH({9.999E+307,"zzzz"},$A:$A))
 
D

Domenic

Interesting...but the drawback with this is that there needs to be at
least one cell containing a numerical value and another one containing a
text value. Otherwise, you get a #N/A. :)
 
D

Don Guillett

Longer but addresses all but truly blank
=IF(ISNA(MATCH(99999,N:N)),MATCH("zzzzzz",N:N),MATCH(99999,N:N))
 
D

Domenic

Don Guillett said:
BTW. OP said there would be a mix.
Click to expand...

Yes, you're right. But I think in this case it's probably best to
provide a solution, like your last formula, that deals with the
potential for different possibilities.
 
A

Aladin Akyurek

Don said:
Longer but addresses all but truly blank
=IF(ISNA(MATCH(99999,N:N)),MATCH("zzzzzz",N:N),MATCH(99999,N:N))
Click to expand...

Apart from invoking risky 99999, what you're suggesting is not robust,
therefore incorrect. Try the suggestion on:

N1: 1
N2: (empty)
N3: =""

That is, A3 houses a formula blank. The result you should get is 3.
 
A

Aladin Akyurek

Don said:
BTW. OP said there would be a mix.
Click to expand...

Great defense. How about the case when a mix doesn't materialize? This
can easily happen if the range of interest is ridden with formulas that
either succeeds producing only numbers or only text values.
 
Z

z.entropic

Thank you-works great!

I'm glad my question has started such a spirited exchange, but in the end it
confused the heck out of me! What's the final word?

z.entropic
 
B

Biff

Hi!
What's the final word?
Click to expand...

Good question!

The method least likely to fail and the one that's most efficient would be
Aladin's. (it usually is!)

This thread is a good representation that demonstrates how many different
ways there are to accomplish a task.

Options are a good thing!

Biff
 
Top