C
Chris
I'm very new to VBA and I would really appreciate some help.
I have a lot of data that's organized as follows:
x y
0 y1
1 y2
2 y3
3 y4
4 y5
....
9 y10
0 y11
1 y12
....
9 y20
0 y21
....
I have created formulas that run regressions on these sets of data.
The x values represent time and right now they all range from 0-9, but
in the future more data points will be added. What I want to do is
write a VBA macro that will allow me to determine how many data points
I want to regress on.
Right now the regression is set to run on all tendata points. My
thought was that the user could input a value, k, into a certain cell
and when the macro is run it will regress on only those last k points.
For example, if the user inputs the value 6 and runs the macro, the
regression will regress on points 4, 5, 6, 7, 8, and 9, i.e. the last
six points.
Since more points can be added in the future, however, the last value
won't be in the same row. So I'm thinking about running a loop that
checks to see if the value of the x is increasing. Once it sees that
the value starts over again at 0, it goes back up and that is the last
value in the list.
An example of one of the formulas I'm using is as follows:
=COVAR(LN($B
$4:$B$13),$A$4:$A$13)/DEVSQ($A$4:$A$13)*COUNT($B$4:$B$13)
Where $B$4:$B$13 are the y values and $A$4:$A$13 are the x values,
with Row 13 being the last value in the list (my code will see this
because the value in A14 is 0). When I run the macro with the value of
6, I want it to go in and change the values to $B$8:$B$13 and $A$8:$A
$13.
Thanks in advance for all your help!
I have a lot of data that's organized as follows:
x y
0 y1
1 y2
2 y3
3 y4
4 y5
....
9 y10
0 y11
1 y12
....
9 y20
0 y21
....
I have created formulas that run regressions on these sets of data.
The x values represent time and right now they all range from 0-9, but
in the future more data points will be added. What I want to do is
write a VBA macro that will allow me to determine how many data points
I want to regress on.
Right now the regression is set to run on all tendata points. My
thought was that the user could input a value, k, into a certain cell
and when the macro is run it will regress on only those last k points.
For example, if the user inputs the value 6 and runs the macro, the
regression will regress on points 4, 5, 6, 7, 8, and 9, i.e. the last
six points.
Since more points can be added in the future, however, the last value
won't be in the same row. So I'm thinking about running a loop that
checks to see if the value of the x is increasing. Once it sees that
the value starts over again at 0, it goes back up and that is the last
value in the list.
An example of one of the formulas I'm using is as follows:
=COVAR(LN($B
$4:$B$13),$A$4:$A$13)/DEVSQ($A$4:$A$13)*COUNT($B$4:$B$13)
Where $B$4:$B$13 are the y values and $A$4:$A$13 are the x values,
with Row 13 being the last value in the list (my code will see this
because the value in A14 is 0). When I run the macro with the value of
6, I want it to go in and change the values to $B$8:$B$13 and $A$8:$A
$13.
Thanks in advance for all your help!