Problems Consolidating Range for Analysis ToolPak Regression

**nb-** · 09-23-2014, 02:32 PM

I am trying to do a number of regressions in excel (2010) using the analysis toolpak regression functions through vba;

The sheets with the data have a number of columns with a heading at the top (sometimes quite a long way above the data) and formulas underneath that return either a value or "" (blank). Sometimes the regression is against 1 variable and sometimes 3 or 4.

Heading 1
Formula Returning Value
Formula Returning Value
Formula Returning Value
Formula Return ""
Formula Return ""
Formula Return ""
Formula Returning Value
Formula Return ""
etc.

Obviously if I try and pass this range to the analysis toolpak, it gives the error; Regression 0 Input range contains non-numeric data. because of all the "" blanks.

Is there a cunning way of using unions and intersect to parse the values to the regression function that I have failed to think of?

My immediate though was to read the data into arrays and consolidate the arrays, but this didn't work either its throwing the error; Regression - Input Y range must be a contiguous reference

Am I calling the regression function in an odd way such that it does not like my arrays as parameters or doing something else silly?

Does anyone have any ideas for a way round my problems? I don't really want to have to copy the values to a new set of columns and operate off the new value columns, which is about all I can think of to do.

Workbook as a reference to ATPVBAEN.XLAM

[side point as you will see I was also having problems with getting the number of dimensions of the array after it has been transposed]

Please Login or Register  to view this content.

**MrShorty** · 09-25-2014, 11:05 AM

Initial disclaimer -- I don't use VBA to run regressions very much, so I'm not very good at it. But, since no one else has responded, I will make a couple of suggestions.

1) I never use the regression tool for performing regressions, so I cannot really comment effectively on using it. For linear regressions, I use the LINEST() function (which is also available to VBA: http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx ). So, any suggestion I will make will be centered around using the LINEST() function directly - rather than going through the regression tool.

2) Based on your reported errors, it appears that, like LINEST(), the regression tool just does not like non-contiguous ranges. I'm not good enough at this kind of programming to say for sure that there is not a clever use of UNION etc to get the effect, but I kind of doubt there is.

Does anyone have any ideas for a way round my problems? I don't really want to have to copy the values to a new set of columns and operate off the new value columns, which is about all I can think of to do.

If I may ask, what is your reason for being "stingy" with your use of a cells in that you don't want to use a helper block to contain the "filtered" results? With 10's of thousands of columns available and a million rows and tabs limited by available memory, I sometimes wonder at our reticence to use additional cells. In many ways, I think a helper block is the easiest solution to your problem, so I'm not sure why we would reject it as a possible solution. (If it helps, Dennis Wallentin's sample code at the end of this help file http://msdn.microsoft.com/en-us/libr.../ff838238.aspx shows how to use the advanced filter tool in VBA to filter a list into another block of cells).

3) If you really do not want to use a helper block, the next obvious solution I see is to "filter" the data into VBA arrays (not range objects). Then you can pass those arrays to the LINEST() function.

**nb-** · 09-29-2014, 11:25 AM

Thanks for your input MrShorty. The regression tool in the analysis toolpak uses the linest function, and that is what creates some of its irritating requirements. I have resorted to helper ranges, I just prefer to avoid relying on things in the sheet that are not just the input range and the output range, I guess from habit of wanting things tidy and not liking to the performance impact of reading and writing to the sheet gratuitously.

My main issue was that it didn't seem to be very happy with the arrays i tried to pass it.

Interesting little article in the link, never used range advanced filter.