Microsoft Office Tutorials and References
In Depth Information
array of values, it seemed too difficult, so Microsoft also offers the
SLOPE and INTERCEPT functions to retrieve the key results from LINEST.
In mathematical terms, a line is described as y = mx + b:
• y — This is the value you are trying to predict. It could be sales for
a given year.
• b — This is called the y-intercept. This is the base level of sales
that you can count on year after year.
• m — This is the slope of the line. If your sales are going up by 1,000
per year, the slope is 1,000. If your sales are going up by 100,000 per
year, the slope is 100,000.
• x — This is a point along the x-axis. In a problem where you are meas-
uring sales over a span of several years, you can assign year num-
bers 1, 2, 3, and so on to each year. x then corresponds to a year num-
If you have a series of year numbers and sales for each year, you need to
calculate both the SLOPE and INTERCEPT to describe the line.
The SLOPE function returns the slope of the linear regression line through
data points in known_y'sand known_x's. The slope is the vertical distance
divided by the horizontal distance between any two points on the line; in other
words, it is the rate of change along the regression line.
The INTERCEPT function calculates the point at which a line intersects the
y-axis by using existing x values and y values. The intercept point is based
on a best-fit regression line plotted through the known x values and known
y values. You use the intercept when you want to determine the value of the
dependent variable when the independent variable is 0.
guage of Excel, these are the known_y's. You are predicting that sales are
increasing linearly over time. The year numbers in A2:A11 are the independent
variables. In the language of Excel, these are the known_x's.