thetxlibra
New Member
- Joined
- Jan 28, 2015
- Messages
- 3
I'm attempting to establish trends and slopes that ignore outliers or inapplicable data. Here is my problem:
1. My data range is 16 columns wide, starting at row 6 (U6 to AJ6). Downward from there is more data in the same format, so we really only need to address one line, with the columns as fixed references, but not the rows (so, $U6 to $AJ6).
2. The Data will usually contain a number, but will sometimes contain NA or ND (short for Not Applicable, or No Data, respectively). Manually sorting to remove the data is not an option, as there are hundreds of inputs the data can be pulled from, and it's in chronological order of weeks, working backward. So, most recent week will be at $U6, 16 weeks ago will be at $AJ6.
3. I need to perform various LINEST functions against the data. However, we need to ignore Outliers.
4. The Outliers have already been calculated, with the Upper Control Limit in column Q and the Lower Control Limit in Column R for each row. (so, in our 1 line example, $Q6 and $R6, respectively). We want our LINEST functions to exclude anything within ($U6:$AJ6) that is higher than $Q6 or lower than $R6.
5. We also want the LINEST formulas to exclude anything with ND or NA in them.
I have been trying to wrap my head around this problem for two days now, and it's driving me batty. I would be sincerely grateful for any assistance y'all can lend. Thank you in advance.
1. My data range is 16 columns wide, starting at row 6 (U6 to AJ6). Downward from there is more data in the same format, so we really only need to address one line, with the columns as fixed references, but not the rows (so, $U6 to $AJ6).
2. The Data will usually contain a number, but will sometimes contain NA or ND (short for Not Applicable, or No Data, respectively). Manually sorting to remove the data is not an option, as there are hundreds of inputs the data can be pulled from, and it's in chronological order of weeks, working backward. So, most recent week will be at $U6, 16 weeks ago will be at $AJ6.
3. I need to perform various LINEST functions against the data. However, we need to ignore Outliers.
4. The Outliers have already been calculated, with the Upper Control Limit in column Q and the Lower Control Limit in Column R for each row. (so, in our 1 line example, $Q6 and $R6, respectively). We want our LINEST functions to exclude anything within ($U6:$AJ6) that is higher than $Q6 or lower than $R6.
5. We also want the LINEST formulas to exclude anything with ND or NA in them.
I have been trying to wrap my head around this problem for two days now, and it's driving me batty. I would be sincerely grateful for any assistance y'all can lend. Thank you in advance.
Last edited:
