Dynamic named range

[chewitt]

Hello all,

I am needing help defining a dynamic named range. I am currently using this:

=OFFSET(Blank!$A$8,0,0,COUNTIF(Blank!$A:$A, ">=0"))

Isn't there a better method of counting the numeric values?

 

[cemserin]

Dear chewitt,

if you want to count all numeric numbers in Column A (including negative numbers) please use below formula.

=COUNTIF(A:A,">=0")+COUNTIF(A:A,"<0")

Is it O.K?

 

[chewitt]

Thanks for your quick reply.

Yes that would work, but I was thinking there should be a better way to represent the criteria (similarly to ISNUMBER()). Quite simply, it does not seem natural to me to do a numeric comparison with a string.

 

[Yogi Anand]

Hi Chewitt:

Are you trying to delineate a Dynamic Range, extending from cell A8 down? -- if so, the following may be of some interest to you ...

Book3
	A	B	C	D
7
8	1		Dyn1	=Sheet5!$A$8:INDEX(Sheet5!$A:$A,MATCH(9.99999999999999E+307,Sheet5!$A:$A))
9	2
10	3		Dyn2	=OFFSET(Sheet5!$A$8,0,0,MATCH(9.99999999999999E+307,Sheet5!$A$8:$A$65536))
11	4
12
13
14	7
15	9
16	8
17
Sheet5

I have created Dyn1 and Dyn2 as dynamic ranges to delineate the cells colored in aqua.

If I have misunderstood your question, my apologies!

 

[Oaktree]

What happens if your data is

50
text
100

Your range would only include the "50" and the "text", as it would only offset by 2 (counting the 50 and the 100).

Maybe =OFFSET(Blank!$A$8,0,0,COUNTa(Blank!$A:$A)) is better?

 

[GorD]

I use the same method as Oaktree - CountA()

 

[Aladin Akyurek]

What happens if your data is

50
text
100

Your range would only include the "50" and the "text", as it would only offset by 2 (counting the 50 and the 100).

Maybe =OFFSET(Blank!$A$8,0,0,COUNTa(Blank!$A:$A)) is better?

Good point if the range is of mixed data type. However, what happens if we have empty cells before the last used cell under your suggestion?

I think the OP should unambiguously state what kind of data there is in column A.

 

[chewitt]

Wow! I have been a developer for 7 years and have had to start using Excel due to a downturn in the economy. I have never had so many people respond to a post so quickly! My thanks to you all.

All of the data will be contiguous and will be numeric. Data above will be text and will be ignored, thus the starting point $A$8.

Here is a better way of asking the question(s):

Is there a better way to represent the COUNTIF() considering it includes the entire column? Can you use ISNUMBER() in conjunction with COUNTIF()?

It "feels" un-natural to do a numeric comparison with text.

 

[Aladin Akyurek]

Wow! I have been a developer for 7 years and have had to start using Excel due to a downturn in the economy. I have never had so many people respond to a post so quickly! My thanks to you all.

All of the data will be contiguous and will be numeric. Data above will be text and will be ignored, thus the starting point $A$8.

Here is a better way of asking the question:
Is there a better way to represent the COUNTIF() considering it includes the entire column?

It "feels" un-natural to do a numeric comparison with text.

=x!$A$8:INDEX(x!$A:$A,MATCH(9.99999999999999E+307,x!$A:$A))

would define a dynamic range, which is insensitive to text and empty cells in between.

If you don't have any empty cells, the following...

=x!$A$8:INDEX(x!$A:$A,COUNTA(x!$A:$A)+CELL("Row",x!$A$8)-1)

will also work

Substitute the relevant sheet name for x.