Iterative process,

Hello,

I need some help please. I'd like to do a simple iterative process of a
linear model. For example,

X values in (A1:A100)
Y values in (B1:B100)

How do I do iteration for Y = A + BX? Thanks

Hi,

If you just want the optimized values of the parameters, A (y-intercept) and
B (slope), do one of the following.

(1) If you want a graphical representation of your data and the linear fit,
make a plot of y (B1:B100) vs x (A1:A100) first, and then
right click on any data point on the plot --> "Add Trendline" --> under
"Options" Tab check "Display equation on chart" --> "OK"

(2) If you only need the optimized values of A and B (and not the graph),
you can just enter the following formulas in two cells.

=SLOPE(B1:B100,A1:A100)
=INTERCEPT(B1:B100,A1:A100)

You can also use LINEST function if you want to obtain the
standard-deviations in A and B as well.

By "How do I do iteration for Y = A + BX?", you meant accomplishing more
than merely obtaining the optimized values of A and B, my apologies for
misunderstanding your question.

Regards,
B. R. Ramachandran

"bioyyy" wrote:

> Hello,
>
> I need some help please. I'd like to do a simple iterative process of a
> linear model. For example,
>
> X values in (A1:A100)
> Y values in (B1:B100)
>
> How do I do iteration for Y = A + BX? Thanks

B.R.,
Thanks for replying, but it's not what I am looking for is to minimise the
sum of squared residuals (SSR) of y after the initial slope and intercept are
determined. So that more reliable slope and intercept estimations are
obtained. Usually it takes about 3-5 iterations. BUT I really don't know how
to this. Thanks again,

"B. R.Ramachandran" wrote:

> Hi,
>
> If you just want the optimized values of the parameters, A (y-intercept) and
> B (slope), do one of the following.
>
> (1) If you want a graphical representation of your data and the linear fit,
> make a plot of y (B1:B100) vs x (A1:A100) first, and then
> right click on any data point on the plot --> "Add Trendline" --> under
> "Options" Tab check "Display equation on chart" --> "OK"
>
> (2) If you only need the optimized values of A and B (and not the graph),
> you can just enter the following formulas in two cells.
>
> =SLOPE(B1:B100,A1:A100)
> =INTERCEPT(B1:B100,A1:A100)
>
> You can also use LINEST function if you want to obtain the
> standard-deviations in A and B as well.
>
> By "How do I do iteration for Y = A + BX?", you meant accomplishing more
> than merely obtaining the optimized values of A and B, my apologies for
> misunderstanding your question.
>
> Regards,
> B. R. Ramachandran
>
> "bioyyy" wrote:
>
> > Hello,
> >
> > I need some help please. I'd like to do a simple iterative process of a
> > linear model. For example,
> >
> > X values in (A1:A100)
> > Y values in (B1:B100)
> >
> > How do I do iteration for Y = A + BX? Thanks

bioyyy wrote:
> B.R.,
> Thanks for replying, but it's not what I am looking for is to minimise the
> sum of squared residuals (SSR) of y after the initial slope and intercept are
> determined. So that more reliable slope and intercept estimations are
> obtained. Usually it takes about 3-5 iterations. BUT I really don't know how
> to this. Thanks again,

-----------------------------

To iterate solutions in some custom method you desire, use Tools>Solver. It
allows you to specifiy some cell to be minimized by twiddling other specified
cells. You can run wild!

Bill

Hi,

The "Trendline" utility does minimize the SSR to optimize the paramters.

You can accomplish the same task with the "Solver" Add-in in Excel, where
minimization of the SSR is more explicit. Please note that the Add-in should
have been installed in Excel for invoking it (for that, "Tools" -->
"Add-Ins", check "Solver Add-In...--> "OK"; Excel might ask for the Microsoft
Excel or Office CD)

Have two helper cells (say E1 and F1) to place the values for A
(y-intercept) and B (slope) respectively. You could just enter 1.0 as
initial guess values for A and B.

In C1, enter the formula =$E$1+$F$1*A1 [This formula calculates Y based on
the guess values of A and B and the X-value in cell A1]. Fill-in the formula
down column to C100 [Thus Column C1:C100 contains calculated y values].
In D1 enter the formula, =SUMXMY2(B1:B100,C1:C100) [This formula calculates
the SSR]
Now, "Tools" --> "Solver" --> for 'Set Target Cell', click on D1 (it would
show up as $D$1), check the "Min" button, and for 'By Changing Cells', select
E1 and F1 (it would show up as $E$1:$F$1), click "Solve" button. Solver
would perform iterations and optimize E1 and F1 to minimize D1 (SSR).

If you want, you can modify the optimization preferences (e.g., making the
convergence criterion more stringent) by clicking on "Options" before
clicking "Solve". If you want to see the outcome of each iteration, check
"Show Iteration Results". Solver would pause after each iteration, and you
can see the updated values of the slope and the y-intercept vis-a-vis the SSR.

Regards,
B. R. Ramachandran




"bioyyy" wrote:

> B.R.,
> Thanks for replying, but it's not what I am looking for is to minimise the
> sum of squared residuals (SSR) of y after the initial slope and intercept are
> determined. So that more reliable slope and intercept estimations are
> obtained. Usually it takes about 3-5 iterations. BUT I really don't know how
> to this. Thanks again,
>
> "B. R.Ramachandran" wrote:
>
> > Hi,
> >
> > If you just want the optimized values of the parameters, A (y-intercept) and
> > B (slope), do one of the following.
> >
> > (1) If you want a graphical representation of your data and the linear fit,
> > make a plot of y (B1:B100) vs x (A1:A100) first, and then
> > right click on any data point on the plot --> "Add Trendline" --> under
> > "Options" Tab check "Display equation on chart" --> "OK"
> >
> > (2) If you only need the optimized values of A and B (and not the graph),
> > you can just enter the following formulas in two cells.
> >
> > =SLOPE(B1:B100,A1:A100)
> > =INTERCEPT(B1:B100,A1:A100)
> >
> > You can also use LINEST function if you want to obtain the
> > standard-deviations in A and B as well.
> >
> > By "How do I do iteration for Y = A + BX?", you meant accomplishing more
> > than merely obtaining the optimized values of A and B, my apologies for
> > misunderstanding your question.
> >
> > Regards,
> > B. R. Ramachandran
> >
> > "bioyyy" wrote:
> >
> > > Hello,
> > >
> > > I need some help please. I'd like to do a simple iterative process of a
> > > linear model. For example,
> > >
> > > X values in (A1:A100)
> > > Y values in (B1:B100)
> > >
> > > How do I do iteration for Y = A + BX? Thanks

B. R.,

Thank you so much for your help. I know that excel can do that, but I just
don't know how to set it up. Again, thanks for taking your time to solve my
problem! VERY VERY HELPFUL!



"B. R.Ramachandran" wrote:

> Hi,
>
> The "Trendline" utility does minimize the SSR to optimize the paramters.
>
> You can accomplish the same task with the "Solver" Add-in in Excel, where
> minimization of the SSR is more explicit. Please note that the Add-in should
> have been installed in Excel for invoking it (for that, "Tools" -->
> "Add-Ins", check "Solver Add-In...--> "OK"; Excel might ask for the Microsoft
> Excel or Office CD)
>
> Have two helper cells (say E1 and F1) to place the values for A
> (y-intercept) and B (slope) respectively. You could just enter 1.0 as
> initial guess values for A and B.
>
> In C1, enter the formula =$E$1+$F$1*A1 [This formula calculates Y based on
> the guess values of A and B and the X-value in cell A1]. Fill-in the formula
> down column to C100 [Thus Column C1:C100 contains calculated y values].
> In D1 enter the formula, =SUMXMY2(B1:B100,C1:C100) [This formula calculates
> the SSR]
> Now, "Tools" --> "Solver" --> for 'Set Target Cell', click on D1 (it would
> show up as $D$1), check the "Min" button, and for 'By Changing Cells', select
> E1 and F1 (it would show up as $E$1:$F$1), click "Solve" button. Solver
> would perform iterations and optimize E1 and F1 to minimize D1 (SSR).
>
> If you want, you can modify the optimization preferences (e.g., making the
> convergence criterion more stringent) by clicking on "Options" before
> clicking "Solve". If you want to see the outcome of each iteration, check
> "Show Iteration Results". Solver would pause after each iteration, and you
> can see the updated values of the slope and the y-intercept vis-a-vis the SSR.
>
> Regards,
> B. R. Ramachandran
>
>
>
>
> "bioyyy" wrote:
>
> > B.R.,
> > Thanks for replying, but it's not what I am looking for is to minimise the
> > sum of squared residuals (SSR) of y after the initial slope and intercept are
> > determined. So that more reliable slope and intercept estimations are
> > obtained. Usually it takes about 3-5 iterations. BUT I really don't know how
> > to this. Thanks again,
> >
> > "B. R.Ramachandran" wrote:
> >
> > > Hi,
> > >
> > > If you just want the optimized values of the parameters, A (y-intercept) and
> > > B (slope), do one of the following.
> > >
> > > (1) If you want a graphical representation of your data and the linear fit,
> > > make a plot of y (B1:B100) vs x (A1:A100) first, and then
> > > right click on any data point on the plot --> "Add Trendline" --> under
> > > "Options" Tab check "Display equation on chart" --> "OK"
> > >
> > > (2) If you only need the optimized values of A and B (and not the graph),
> > > you can just enter the following formulas in two cells.
> > >
> > > =SLOPE(B1:B100,A1:A100)
> > > =INTERCEPT(B1:B100,A1:A100)
> > >
> > > You can also use LINEST function if you want to obtain the
> > > standard-deviations in A and B as well.
> > >
> > > By "How do I do iteration for Y = A + BX?", you meant accomplishing more
> > > than merely obtaining the optimized values of A and B, my apologies for
> > > misunderstanding your question.
> > >
> > > Regards,
> > > B. R. Ramachandran
> > >
> > > "bioyyy" wrote:
> > >
> > > > Hello,
> > > >
> > > > I need some help please. I'd like to do a simple iterative process of a
> > > > linear model. For example,
> > > >
> > > > X values in (A1:A100)
> > > > Y values in (B1:B100)
> > > >
> > > > How do I do iteration for Y = A + BX? Thanks