using linest function for 2nd order polynomial

[balan]

Hi All,

I am using linest function to get coefficient of 2nd order polynomial {y = (c2*x^2)+(c1*x) + c0 } for the following variable,

250	1.3
500	3.8
750	7.2
1000	11.4
1250	16.2
1500	21.6
1750	27.6
2000	34.1
2250	41.1
2500	48.6

<colgroup><col width="64"><col width="64"></colgroup><tbody>
</tbody>

and using the following equation
c2 = index(linest(y,x^{1,2},1)
c1 = index(linest(y,x^{1,2},1,2)
c0 = index(linest(y,x^{1,2},1,3) and these do calculate values as
c2 = 0.0000048, c1 = 0.008 and c0 = -1.25. But when I keep input variables in row wise(as shown below), I am not able to use these equations. Looking for a help.

250.0	500.0	750.0	1000.0	1250.0	1500.0	1750.0	2000.0	2250.0	2500.0
1.3	3.8	7.2	11.4	16.2	21.6	27.6	34.1	41.1	48.6

<colgroup><col width="64"><col width="64"><col width="64"><col width="64"><col width="64"><col width="64"><col width="64"><col width="64"><col width="64"><col width="64"></colgroup><tbody>
</tbody>

Thanks in advance for any help someone may be able to give.

<colgroup><col width="97"></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Have a look at the TRANSPOSE function.

 

[balan]

Hello Aladin,

If I convert variables from row format to column format by using this transpose function, it works. But If I can able to use this linest function when these variables in row format, it will be better for me. Because this method I am using for finding optimum design point from 'n' set of design points.

 

[Aladin Akyurek]

Hello Aladin,

If I convert variables from row format to column format by using this transpose function, it works. But If I can able to use this linest function when these variables in row format, it will be better for me. Because this method I am using for finding optimum design point from 'n' set of design points.

Vertical data...

Control+shift+enter, not just enter:

=INDEX(LINEST(A1:A10,B1:B10^{1,2}),1)

Horizontal data...

Control+shift+enter, not just enter:

=INDEX(LINEST(TRANSPOSE(A1:J1),TRANSPOSE(A2:J2)^{1,2}),1)

Both produce the same outcome.

 

[balan]

HI Aladin,

Thank You for your reply. It works well and saves lot of time during my optimization study.

Many thanks once again.

 

[Aladin Akyurek]

HI Aladin,

Thank You for your reply. It works well and saves lot of time during my optimization study.

Many thanks once again.

You are welcome.

 

[pgc01]

Hi All,

I am using linest function to get coefficient of 2nd order polynomial {y = (c2*x^2)+(c1*x) + c0 } for the following variable,

250	1.3
500	3.8
750	7.2
1000	11.4
1250	16.2
1500	21.6
1750	27.6
2000	34.1
2250	41.1
2500	48.6

<tbody>
</tbody>

and using the following equation
c2 = index(linest(y,x^{1,2},1)
c1 = index(linest(y,x^{1,2},1,2)
c0 = index(linest(y,x^{1,2},1,3) and these do calculate values as
c2 = 0.0000048, c1 = 0.008 and c0 = -1.25. But when I keep input variables in row wise(as shown below), I am not able to use these equations. Looking for a help.

250.0	500.0	750.0	1000.0	1250.0	1500.0	1750.0	2000.0	2250.0	2500.0
1.3	3.8	7.2	11.4	16.2	21.6	27.6	34.1	41.1	48.6

<tbody>
</tbody>

Thanks in advance for any help someone may be able to give.

<tbody>
</tbody>

Hi

Another option

Since the data and the exponents array must be orthogonal, if your data is in horizontal rows, like in the second table, use like:

c2 = index(linest(y,x^{1;2},1)

 

[Aladin Akyurek]

Hi

Another option

Since the data and the exponents array must be orthogonal, if your data is in horizontal rows, like in the second table, use like:

c2 = index(linest(y,x^{1;2},1)

Good idea... Adding the missing paren:

=INDEX(LINEST(A1:J1,A2:J2^{1;2}),1)

 

[balan]

Hi..

Nice option. Thank you for sharing.

 

[pgc01]

You're welcome.

@Aladin: thank you.