Iterative process,

[Guest]

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

 

[Guest]

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

 

[Guest]

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,

 

[Bill Martin]

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

 

[Guest]

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

 

[Guest]

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!

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

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,