A hopefully simple question about SLOPE()...

[Guest]

Howdy
I have two columns of data (X and Y values, a simple line) that I need to find the slope and y-intercept of with VBA. I've read about WorksheetFunction.Slope() and .Intercept as well as Index(LinEst()1), but my problem stems from my two datasets existing in array form. I retrieve the data from columns on a sheet, but perform several normalizing functions on the arrays before needing slope and intercept
The worksheet functions talk about accepting arrays as data, and you can pass the functions worksheet arrays {1;2;3;...}, but nothing doing in VBA. I've tried leaving the arrays two dimentional as well as converting them to one dimention per set. Any ideas, or am I going to have to pass the arrays back to a range and pass that to the function

Much appreciated
-Dustin Carter

 

[Tom Ogilvy]

varrX = Array(1,2,3,4,5)
varrY = Array(5,7,3,6,8)
? worksheetFunction.Slope(varry,varrx)
0.5
? worksheetFunction.Intercept(varry,varrx)
4.3

works fine for me.

--
Regards,
Tom Ogilvy

Howdy,
I have two columns of data (X and Y values, a simple line) that I need

to find the slope and y-intercept of with VBA. I've read about
WorksheetFunction.Slope() and .Intercept as well as Index(LinEst()1), but my
problem stems from my two datasets existing in array form. I retrieve the
data from columns on a sheet, but perform several normalizing functions on
the arrays before needing slope and intercept.

The worksheet functions talk about accepting arrays as data, and you can

pass the functions worksheet arrays {1;2;3;...}, but nothing doing in VBA.
I've tried leaving the arrays two dimentional as well as converting them to
one dimention per set. Any ideas, or am I going to have to pass the arrays
back to a range and pass that to the function?