I have a sheet with data in column A, e.g. rows 1 to 100 all have data, but
row 10 to 30 need to be excluded because invalid (but are not empry!). This
column of data is dynamically growing with valid and invalid data. I can
define 2 ranges, from 1-9, 31-100 and perfectly make average over all ranges
together with formula =AVERAGE(range1,range2). In time, more ranges need to
be defined based on number of invalid data not included for analysis.
However, there's a few commands I can't use, e.g. countif
(range1,range2,"40").
How can I solve this problem ? keep on making ranges as data grows ? or can
I run a macro when opening the file which merges range1 and 2 ?

Hi,

what is the factor that determines when something is not valid?

And by the way, if you select A1:A9,A31:A100 you can define a name for the
non-contiguous selection.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"rolando" wrote:

I have a sheet with data in column A, e.g. rows 1 to 100 all have data, but
row 10 to 30 need to be excluded because invalid (but are not empry!). This
column of data is dynamically growing with valid and invalid data. I can
define 2 ranges, from 1-9, 31-100 and perfectly make average over all ranges
together with formula =AVERAGE(range1,range2). In time, more ranges need to
be defined based on number of invalid data not included for analysis.
However, there's a few commands I can't use, e.g. countif
(range1,range2,"40").
How can I solve this problem ? keep on making ranges as data grows ? or can
I run a macro when opening the file which merges range1 and 2 ?

factor for invalid content has different reasons and cannot be automated.
Even when the cell content is the same as others, it still might be declared
as 'invalid'.
however, all invalid cells are colored in red font instead of black.
I agree that multiple ranges can be defined as one named range but still the
formulas average and countif doesn't work on this range, get error message
#N/A and #VALUE respectively

"Shane Devenshire" wrote:

Hi,

what is the factor that determines when something is not valid?

And by the way, if you select A1:A9,A31:A100 you can define a name for the
non-contiguous selection.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"rolando" wrote:

I have a sheet with data in column A, e.g. rows 1 to 100 all have data, but
row 10 to 30 need to be excluded because invalid (but are not empry!). This
column of data is dynamically growing with valid and invalid data. I can
define 2 ranges, from 1-9, 31-100 and perfectly make average over all ranges
together with formula =AVERAGE(range1,range2). In time, more ranges need to
be defined based on number of invalid data not included for analysis.
However, there's a few commands I can't use, e.g. countif
(range1,range2,"40").
How can I solve this problem ? keep on making ranges as data grows ? or can
I run a macro when opening the file which merges range1 and 2 ?