Microsoft Office Tutorials and References

In Depth Information

**Syntax:**

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-

ber.

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.

Syntax:

=SLOPE(known_y's,known_x's)

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.

Syntax

=INTERCEPT(known_y's,known_x's)

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.

In
Figure 14.14
,
the sales in B2:B11 are the dependent variables. In the lan-

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.