create a dynamic array skipping #N/A and blank rows?

Originally Posted by William DeLeo

Greetings,

I am working with the SLOPE and RSQ functions. The x and y data span the same range, but some of the y data is missing. For the missing data I can either have blank cells or insert #N/A. The data charts fine with the missing data set to #N/A (it ignores NA xy pairs), but the SLOPE and RSQ formula I am using within the worksheet either counts the blank cells as zero (and returns the wrong value), or returns NA if the NAs are present instead. (Note that the missing data cells have lookups so the cells are not totally empty; the returned value for an error in the lookup is set to #N/A so the data charts correctly.)

So, my question: with straight cell functions or named ranges, how can I create two dynamic arrays as input to the SLOPE and RSQ functions, one for column A data and one for column B data (no blanks), that skips all rows containing #N/A in column B?

TIA!
WD

Hi,

if I have read your question correctly the attached should help, in B put

=OFFSET(B2,0,COUNTIF($A3:A3,#N/A)+COUNTIF($B3:B3,#N/A))
=OFFSET(B3,0,COUNTIF($A3:A3,#N/A)+COUNTIF($B3:B3,#N/A))

but note that your data (more correctly this formula) cannot start in column A

note, your range may need to be adjusted by the #N/A count

hth
---

Originally Posted by William DeLeo

Thank you for your reply and effort. But, though your approach does produce a range of cells with the NA rows removed, I can't see how I could use that without creating a copy of the modified arrays somewhere else in the workbook. Since I have many of these calculations to do in parallel, I was hoping to not reproduce the data elsewhere in the workbook but instead do it all with one array formula for each column of y data.

For clarity:

- this is a data processing and charting automation task

- Column A holds time/date data. If we say there are 50 dates presently in the workbook, then column A rows 1:50 would each contain a unique date (x axis data).

- Columns B:AN represent different locations. For each row in column A that holds a date (i.e. rows 1:50), the cells in columns B:AN (namely B1:AN50) contain lookup formula. If the data exists on a separate sheet for that location on that date, then a value is returned. Otherwise, #N/A is returned.

This table needs to be the source for a series of charts, one chart for each column B:AN. Having #N/A does not cause a problem with the charting. But, in the title block of each chart I need to post some statistics based on the slope of the regression line. To do so I believe I need to calculate it elsewhere and pull the cell value into the chart title. I don't want to create a parallel table with date/value, date/value, date/value ... just to calculate the slope (too calc heavy). But when I try to use SLOPE() with the full range of 1:50 for both x and y, the presence of the NAs in the y data causes an error. And when I try to return "" instead of #N/A when the vlookup comes up empty, the slope calculates right in the worksheet, but the chart treats the blank cells as zero and doesn't plot nor calculate the regression correctly (Ugh!)

So ... for each column B:AN, I need to use a cell formula to extract a non-NA subset of rows 1:50 for each of B:AN, as well as an array of each ones corresponding column A values. Those arrays need to supply the x and y data needed by the SLOPE function.

that was wordy, thanks so much for your help!

WD

I believe that you will find that #N/A is not a value as much as it is a state (or condition), thus when a formula encounters #N/A that status is retained.

If this is correct then you will not be able to extract from an array only those figures which are not #N/A, nor will you, within the array, be able to substitute the #N/A for a value, the substitution not being possible because #N/A is not a value, but merely the display of an error condition.

However, having said that, I have also noted the lists of 'things' applicable to formula and to VB code, and that at least 95% of these lists are information that I have not yet learned, so I wish you good luck with your request, but note that this question is for a more skilled Excel user.

---