Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trend Line constants as Excel Cell Values
I want to put the constants of a 2 degree polynomial trend line into Excel
Cells. Are there formula's to do this? Equation is Y = Ax(2) + Bx + C. I want the value of A,B and C in seperate cells. Can anyone suggest how this can be done? Thanks -- Ken |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trend Line constants as Excel Cell Values
Hi Ken,
Supposing your x-range is in A2:A10, and y-range in B2:B10, select a 3-column x 1-row area and enter the following formula and confirm with CTRL-SHIFT-ENTER. =LINEST(B2:B10,A2:A10^{0,1,2},0,) Regards, B. R. Ramachandran "Ken" wrote: I want to put the constants of a 2 degree polynomial trend line into Excel Cells. Are there formula's to do this? Equation is Y = Ax(2) + Bx + C. I want the value of A,B and C in seperate cells. Can anyone suggest how this can be done? Thanks -- Ken |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trend Line constants as Excel Cell Values
Thanks B. R.Ramachandran again for your great assistance. Ken
-- Ken "B. R.Ramachandran" wrote: Hi Ken, Supposing your x-range is in A2:A10, and y-range in B2:B10, select a 3-column x 1-row area and enter the following formula and confirm with CTRL-SHIFT-ENTER. =LINEST(B2:B10,A2:A10^{0,1,2},0,) Regards, B. R. Ramachandran "Ken" wrote: I want to put the constants of a 2 degree polynomial trend line into Excel Cells. Are there formula's to do this? Equation is Y = Ax(2) + Bx + C. I want the value of A,B and C in seperate cells. Can anyone suggest how this can be done? Thanks -- Ken |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trend Line constants as Excel Cell Values
How would this work for a Polynominal degree 6? X-range covers 52 points.
"B. R.Ramachandran" wrote: Hi Ken, Supposing your x-range is in A2:A10, and y-range in B2:B10, select a 3-column x 1-row area and enter the following formula and confirm with CTRL-SHIFT-ENTER. =LINEST(B2:B10,A2:A10^{0,1,2},0,) Regards, B. R. Ramachandran "Ken" wrote: I want to put the constants of a 2 degree polynomial trend line into Excel Cells. Are there formula's to do this? Equation is Y = Ax(2) + Bx + C. I want the value of A,B and C in seperate cells. Can anyone suggest how this can be done? Thanks -- Ken |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trend Line constants as Excel Cell Values
Hi Andrew,
Let's suppose that the x- and y- ranges are in A2:A53 and B2:B53 respectively. Select a 7 column x 1 row area, enter the following formula, and confirm with CTRL-SHIFT-ENTER. =LINEST(B2:B53,A2:A53^{0,1,2,3,4,5,6},0,) The formula will return the 7 coefficients, starting from the zeroth order and ending with the 6th order. If you want to have the coefficients shown starting from the 6th order and ending in the zeroth order, modify the formula as, =LINEST(B2:B53,A2:A53^{6,5,4,3,2,1,0},0,) Regards, B. R. Ramachandran "Andrew" wrote: How would this work for a Polynominal degree 6? X-range covers 52 points. "B. R.Ramachandran" wrote: Hi Ken, Supposing your x-range is in A2:A10, and y-range in B2:B10, select a 3-column x 1-row area and enter the following formula and confirm with CTRL-SHIFT-ENTER. =LINEST(B2:B10,A2:A10^{0,1,2},0,) Regards, B. R. Ramachandran "Ken" wrote: I want to put the constants of a 2 degree polynomial trend line into Excel Cells. Are there formula's to do this? Equation is Y = Ax(2) + Bx + C. I want the value of A,B and C in seperate cells. Can anyone suggest how this can be done? Thanks -- Ken |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trend Line constants as Excel Cell Values
Very nice solution! Is there a way to present the resulting coefficients in
the same column as the data? Or, better yet, to predict the value of Y from a New X and known Xs and known Ys that fit well to a polynomial? "B. R.Ramachandran" wrote: Hi Andrew, Let's suppose that the x- and y- ranges are in A2:A53 and B2:B53 respectively. Select a 7 column x 1 row area, enter the following formula, and confirm with CTRL-SHIFT-ENTER. =LINEST(B2:B53,A2:A53^{0,1,2,3,4,5,6},0,) The formula will return the 7 coefficients, starting from the zeroth order and ending with the 6th order. If you want to have the coefficients shown starting from the 6th order and ending in the zeroth order, modify the formula as, =LINEST(B2:B53,A2:A53^{6,5,4,3,2,1,0},0,) Regards, B. R. Ramachandran "Andrew" wrote: How would this work for a Polynominal degree 6? X-range covers 52 points. "B. R.Ramachandran" wrote: Hi Ken, Supposing your x-range is in A2:A10, and y-range in B2:B10, select a 3-column x 1-row area and enter the following formula and confirm with CTRL-SHIFT-ENTER. =LINEST(B2:B10,A2:A10^{0,1,2},0,) Regards, B. R. Ramachandran "Ken" wrote: I want to put the constants of a 2 degree polynomial trend line into Excel Cells. Are there formula's to do this? Equation is Y = Ax(2) + Bx + C. I want the value of A,B and C in seperate cells. Can anyone suggest how this can be done? Thanks -- Ken |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trend Line constants as Excel Cell Values
To put all the coefficients in a column, use the TRANSPOSE function.
To get predicted Y values, use the TREND function. For this model, the syntax is =TREND(B2:B53,A2:A53^{0,1,2,3,4,5,6},prediction_x_ vals,0,) If you just want the predicted values for the original data, you can omit the third argument =TREND(B2:B53,A2:A53^{0,1,2,3,4,5,6},,0,) Jerry "gdors" wrote: Very nice solution! Is there a way to present the resulting coefficients in the same column as the data? Or, better yet, to predict the value of Y from a New X and known Xs and known Ys that fit well to a polynomial? "B. R.Ramachandran" wrote: Hi Andrew, Let's suppose that the x- and y- ranges are in A2:A53 and B2:B53 respectively. Select a 7 column x 1 row area, enter the following formula, and confirm with CTRL-SHIFT-ENTER. =LINEST(B2:B53,A2:A53^{0,1,2,3,4,5,6},0,) The formula will return the 7 coefficients, starting from the zeroth order and ending with the 6th order. If you want to have the coefficients shown starting from the 6th order and ending in the zeroth order, modify the formula as, =LINEST(B2:B53,A2:A53^{6,5,4,3,2,1,0},0,) Regards, B. R. Ramachandran |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trend Line constants as Excel Cell Values
If you are trying to solve for x knowing all the other terms, rearange the
equation: x^2+Bx/A +C-Y =0 to ax^2+bx+c=0 Then use the solution for the roots: (-b+-sqrt(b^2-4ac))/2a "Ken" wrote: I want to put the constants of a 2 degree polynomial trend line into Excel Cells. Are there formula's to do this? Equation is Y = Ax(2) + Bx + C. I want the value of A,B and C in seperate cells. Can anyone suggest how this can be done? Thanks -- Ken |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I subtract excel cell values that contain both a number a. | Excel Discussion (Misc queries) | |||
How do I Auto-Filter with multiple values in a cell in Excel? | Excel Discussion (Misc queries) | |||
how do i get excel to automatically compute cell values | Excel Discussion (Misc queries) | |||
How di get excel to automatically change cell values | Excel Discussion (Misc queries) | |||
Remove the apostrophe (') in Excel cell text values | Excel Discussion (Misc queries) |