Star your own bussiness today


helpyourbiz.org keyword stats



Most current MSN search phrases:

internet success
economic business
looking opportunities
there grant
bussiness  

Linear Regression With Microsoft Excel

You use Excel's linear regression functionsand b given at least one set of known ys and
to find a linear equation that best describesknown xs. The LINEST function has the
a  data  set.following  syntax:
Excel uses the sum of least squares method to=LINEST (known ys, known xs, constant,
find the straight line of best fit. Peoplestatistics)where known ys is the array of y
oftentry to predict future amounts byvalues you already know, known xs is the
assuming linear growth and extending the linearray of x values you may already know. If
forward intime. For example, if you have ayou leave out the known xs, they are assumed
series of sales data for 9 months and want toto be 1, 2, 3,…n. If constant is set to
predict thesales in the 10th month, you canFALSE, b is assumed to be 0. If statistics is
use Excel's linear regression functions toset to TRUE, the LINEST function also returns
find the slope andy-intercept (the point onthe  standard  error  for  each  data  point.
the y-axis where the line crosses) of the
line  that  best  fits  the  data.NOTE: If the known ys are in a single column
or row, then Excel considers each column
Some  Background  Info  on  Linear Regressionofknown  xs  to  be  a  separate  variable.
To use the linear regression functions, itNOTE: The array known xs can include multiple
helps to remember the equation for asets of variables. If you use only one set,
line:y=mx+bwhere y is the dependent variable,then known ys and known xs can be ranges of
m the slope, x the independent variable, andany shape, as long as they have equal
b they-intercept. If there are multipledimensions. If you use more than one
ranges of x values, the equation looks likevariable, then the known ys array must be
this:y=m1  x1+m2x2+.mnxn+beither a single column or a single row. If
you don't enter known xs, Excel assumes this
NOTE To visualize and experiment with lineararray is the same size as the known ys array.
regression, visit the interactive web page at
Click thegraph area to add data points (x,y)Using  the  SLOPE  Function
to the graph. The applet draws the straight
linethat best fits the points you add,Use the SLOPE function to find the slope (m)
adjusting the line for the new data pointsof the linear regression line from the known
you  add.x and known y data sets. The slope is the
change in y over the change in x for any two
Using  the  FORECAST  Functionpoints on the line. The SLOPE function in
Excel  uses  the  following  syntax:
The FORECAST function predicts a future
y-value for the x-value you specify using=SLOPE  (known  ys,  known  xs)
existingx and y values. The FORECAST function
uses  the  following  syntax:A positive (upwards) slope means that the
independent variable (such as the number of
=FORECAST(x, known ys, known xs)where x issalespeople) has a positive effect on a
the x-value for which you want to predict adependent variable (such as sales). A
y-value.negative (downwards) slope means that the
independent variable has a negative effect on
Using  the  INTERCEPT  Functionthe dependent variable. The steeper the
slope, the more effect the independent
If you have existing x and y values, Excelvariable  has  on  the  dependent  variable.
can find the straight line that best fits the
data and then calculate the point at whichUsing  the  STEYX  Function
the line intersects the y-axis, in other
words, the value of b in the "y=mx+b"Use the STEYX function to find the standard
equation. The y-intercept is useful when youerror of the predicted y-value for each
want to know the value of the dependentindividual x in the regression. The STEYX
variable when the independent variable equalsfunction  uses  the  following  syntax:
0.
=STEYX  (known  ys,  known  xs)
NOTE: The INTERCEPT function returns the same
value as the FORECAST function if you enter 0Using  the  TREND  Function
for  x  in  the  FORECAST  function.
Use the TREND function to find values along a
The INTERCEPT function uses the followinglinear trend. Specify an array of new xs and
syntax:the TREND function uses the method of least
squares to fit a straight line to the known x
=INTERCEPT  (known  ys,  known  xs)and y data sets and return the y-values along
the line for the new array. If constant is
Using  the  LINEST  Functionset to FALSE, the "b" in the y=mx+b equation
is set to zero.
The LINEST function returns the value of m



1 A B C D E F 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132