If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
merge named ranges ?
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 ? |
#2
|
|||
|
|||
merge named ranges ?
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 ? |
#3
|
|||
|
|||
merge named ranges ?
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 ? |
Thread Tools | |
Display Modes | |
|
|