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