Excel Statistics Functions SLOPE and INTERCEPT

Excels Data Analysis For Line Of Best Fit and Linear Regression

Oct 27, 2009

Excel provides two easy-to-use functions called SLOPE and INTERCEPT. These give the line of best fit for a set of data points. This article describes how to use them.

Excel functions for analyzing data include two that are particularly useful - SLOPE and INTERCEPT. They are used to determine how a response on the y-axis is controlled by an input, on the x-axis. It could be used, for example, to determine how does the number of years spent in education affect salary. The relationship between them is assumed to be of the form:

Excel Data Analysis - Data Set Up

To use the SLOPE and INTERCEPT functions in Excel, pairs of data are provided. Figure 1 shows an example, where the salary of ten people is listed, along with how many years they spent at school. Note that several people spent 16 years at school, but have different salaries, and the Excel functions allow this.

It is also permissable to have missing data, but Excel errors like #N/A and #NUM! are not allowed.

Excel SLOPE and INTERCEPT Function Usage

The functions are very easy to use, like most Excel statistics functions. For the example shown in Figure 1, the functions would be:

Note: The example uses fictional data, to illustrate how the functions are used.

Excel SLOPE Function - What Does It Mean?

Analyzing data and using statistics means more than producing numbers - it means interpreting the result into a real-life situation. In the example shown, the SLOPE represents the average salary gain for each year spent in education.

In basic terms, it means that one extra year in education will typically mean a person earns $1879 more, two extra years in education gives $3758 etc.

Excel INTERCEPT Function - Interpretation

In the example shown, and in statistics generally, the intercept is the expected value of "y" when "x" is equal to zero. In this example, it is an estimate of expected salary for a person who spends no years in education.

Using statistics like these needs to be done carefully, as there are potential problems, such as "extrapolation" - calculating a value of "y" for a value of "x" that is outside the range of x-values used to produce the slope and intercept.

Excel SLOPE and INTERCEPT Functions - Other Potential Problems

Statistics books and statistics courses, without exception, all warn of the dangers of using slope and intercept data. That is because the very use of the functions assumes that the control factor (in this case "Years in Education") and the output ("Salary") actually have a linear relationship. In many cases the two parameters are not related in this way.

Before using the functions, a graph plot should be made to check whether it is valid to use the functions, as shown in Figure 2.

Excel SLOPE and INTERCEPT Functions Summary

Microsoft Excel provides many useful statistical functions - SLOPE and INTERCEPT - that are very easy to use, and which can quickly fit the best relationship between two factors. They need to be used carefully, but when used correctly they provide a very useful data analysis tool. The values can also be obtained using the options on an Excel Graph.

More information about Excel functions is available free on-line, or in Excel for Dummies by Greg Harvey.

The copyright of the article Excel Statistics Functions SLOPE and INTERCEPT in Office/Business Software is owned by Martin Bell. Permission to republish Excel Statistics Functions SLOPE and INTERCEPT in print or online must be granted by the author in writing.