Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Iterative process,
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Iterative process,
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Iterative process,
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 ToolsSolver. It allows you to specifiy some cell to be minimized by twiddling other specified cells. You can run wild! Bill |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Iterative process,
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Iterative process,
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
force excel to run in only one process excel.exe | Excel Worksheet Functions | |||
Is it possible to use 'grid' as a value in vb to process XL data? | Excel Discussion (Misc queries) | |||
Excel save process in Windowws 98? | Excel Discussion (Misc queries) | |||
search multiple worksheets name with common text and process using | Excel Worksheet Functions | |||
Simplify Process with Excel 2003 | New Users to Excel |