how can I minimised Sum of squared errors...

[Katerina]

Hello there

I have the quadratic regression model y=α+β*x+γ*(x)^2+ε,α,β,γ are unknown
parametres and ε is a random term representing experimental error.
I have that x=0,1,2,3,4,5,6
y=1,4,11,14,21,30,45
I want to fdetermine a,β,γ when the SSE is minimised
the sum of absolute values of the errors is minimised?
and the maximum absolute value of the error is minimised

What I have done is:
I gοt using Excel 4 columns to represent
x,y,y(predicted),y-y(predicted),[y-y(predicted)]^2 for each y,x

and at last one cell to represent SSE:Σ[y-y(predicted)]^2.
and three cells represent the α,β,γ

for a) I want to minimised the SSE by changing cells α,β,γ.
without ε,because we want to estimate and E(ε)=0.(is this correct or I have
to include 7 different ε1,ε2,.... in our y predicted and then I will use them
inthe changing cells with α,β,γ)

for b) I have the column of y-y(predicted)(or I need y(predicted)-y) for
each y.After that I get a column with the abs of previous column.
and one cell with Σ[y(predicted)-y](or other wise) of the abs values.This is
the cell that I want to minimised by changing cells again a,β,γ.

and at last for c) I got the maximum abs value of the error.This is the cell
that I want to minimised by changing cell again α,β,γ.

I am looking forward to hearing you
thanks a lot

 

[Bernard Liengme]

1) If D110 holds the know y-values and E1:E10 holds the predicted
y-values, then the formula
=SUMXMY2(D110,E1:E10) will give you sum-of-deviations-squared
2) You can use Solver (Tools/Solver) to minimize this quantity by varying
the three parameters

3) You can avoid all this work by using the functions SLOPE, INTERCEPT and
RSQ or do it all at once and get more data with LINEST. Have a look in Help
and come back with questions.

Send me private email (remove TRUENORTH.) and I will send you a pdf file
"Regression with Excel"
best wishes

 

[Jerry W. Lewis]

Simultaneously select 3 blank cells in a row
With the 3 cells selected, click on the formula bar (labeled fx), type the
formula
=LINEST(ydata,xdata^{1,2})
where ydata and xdata are the appropriate colum ranges, and commit the
formula with Ctrl-Shift-Enter. If you do it right, you will get the numbers
0.928571429 1.357142857 1.857142857
which are respectively gamma, beta, and alpha.

If instead you select 5 rows by 3 columns and modify the formula to
=LINEST(ydata,xdata^{1,2},,TRUE)
then the above coefficients will be the first row of output, and the 2nd
column of the 5th row will have the corresponding minimized SSE.

Jerry

 

[Jerry W. Lewis]

SLOPE, INTERCEPT and RSQ are limited to simple linear regression. Since the
OP wants to fit a quadratic polynomial, LINEST (or the ATP regression tool)
is the only option to avoid bute force with Solver.

Jerry

1) If D110 holds the know y-values and E1:E10 holds the predicted
y-values, then the formula
=SUMXMY2(D110,E1:E10) will give you sum-of-deviations-squared
2) You can use Solver (Tools/Solver) to minimize this quantity by varying
the three parameters

3) You can avoid all this work by using the functions SLOPE, INTERCEPT and
RSQ or do it all at once and get more data with LINEST. Have a look in Help
and come back with questions.

Send me private email (remove TRUENORTH.) and I will send you a pdf file
"Regression with Excel"
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Hello there

I have the quadratic regression model y=?+?*x+?*(x)^2+?,?,?,? are unknown
parametres and ? is a random term representing experimental error.
I have that x=0,1,2,3,4,5,6
y=1,4,11,14,21,30,45
I want to fdetermine a,?,? when the SSE is minimised
the sum of absolute values of the errors is minimised?
and the maximum absolute value of the error is minimised

What I have done is:
I g?t using Excel 4 columns to represent
x,y,y(predicted),y-y(predicted),[y-y(predicted)]^2 for each y,x

and at last one cell to represent SSE:?[y-y(predicted)]^2.
and three cells represent the ?,?,?

for a) I want to minimised the SSE by changing cells ?,?,?.
without ?,because we want to estimate and E(?)=0.(is this correct or I
have
to include 7 different ?1,?2,.... in our y predicted and then I will use
them
inthe changing cells with ?,?,?)

for b) I have the column of y-y(predicted)(or I need y(predicted)-y) for
each y.After that I get a column with the abs of previous column.
and one cell with ?[y(predicted)-y](or other wise) of the abs values.This
is
the cell that I want to minimised by changing cells again a,?,?.

and at last for c) I got the maximum abs value of the error.This is the
cell
that I want to minimised by changing cell again ?,?,?.

I am looking forward to hearing you
thanks a lot

 

[Bernard Liengme]

Yes, I did not read the message carefully.
The OP and I have since communicated off-group and I sent her a sample file
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

SLOPE, INTERCEPT and RSQ are limited to simple linear regression. Since
the
OP wants to fit a quadratic polynomial, LINEST (or the ATP regression
tool)
is the only option to avoid bute force with Solver.

Jerry

1) If D110 holds the know y-values and E1:E10 holds the predicted
y-values, then the formula
=SUMXMY2(D110,E1:E10) will give you sum-of-deviations-squared
2) You can use Solver (Tools/Solver) to minimize this quantity by varying
the three parameters

3) You can avoid all this work by using the functions SLOPE, INTERCEPT
and
RSQ or do it all at once and get more data with LINEST. Have a look in
Help
and come back with questions.

Send me private email (remove TRUENORTH.) and I will send you a pdf file
"Regression with Excel"
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Hello there

I have the quadratic regression model y=?+?*x+?*(x)^2+?,?,?,? are
unknown
parametres and ? is a random term representing experimental error.
I have that x=0,1,2,3,4,5,6
y=1,4,11,14,21,30,45
I want to fdetermine a,?,? when the SSE is minimised
the sum of absolute values of the errors is minimised?
and the maximum absolute value of the error is minimised

What I have done is:
I g?t using Excel 4 columns to represent
x,y,y(predicted),y-y(predicted),[y-y(predicted)]^2 for each y,x

and at last one cell to represent SSE:?[y-y(predicted)]^2.
and three cells represent the ?,?,?

for a) I want to minimised the SSE by changing cells ?,?,?.
without ?,because we want to estimate and E(?)=0.(is this correct or I
have
to include 7 different ?1,?2,.... in our y predicted and then I will
use
them
inthe changing cells with ?,?,?)

for b) I have the column of y-y(predicted)(or I need y(predicted)-y)
for
each y.After that I get a column with the abs of previous column.
and one cell with ?[y(predicted)-y](or other wise) of the abs
values.This
is
the cell that I want to minimised by changing cells again a,?,?.

and at last for c) I got the maximum abs value of the error.This is the
cell
that I want to minimised by changing cell again ?,?,?.

I am looking forward to hearing you
thanks a lot