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 ?