A hopefully simple question about SLOPE()...

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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


dcarter said:
Howdy,
I have two columns of data (X and Y values, a simple line) that I need
Click to expand...
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
Click to expand...
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?
 
Back
Top