INTERCEPT Function in Microsoft Excel

From ForcaWiki, the simple encyclopedia

Picture a taxi ride. Before the car even moves an inch, the meter already shows a starting fee. Then, as the taxi drives, the price climbs a little more for every mile. If you graphed the cost against the miles driven, that starting fee, the price at zero miles, is called the "intercept." It is where the line crosses the axis before anything has happened yet.

The INTERCEPT function in Microsoft Excel finds that same kind of starting point, but for your own data. Give it a list of measurements you have collected, and it draws the single straight line that fits those points as closely as possible, then tells you exactly where that line would start.

A worked example

Suppose you are growing a bean plant for a science project, and you measure its height every week:

• Week 1: 3 centimeters
• Week 2: 5 centimeters
• Week 3: 7 centimeters
• Week 4: 9 centimeters

Each week the plant grows by exactly 2 centimeters, a perfectly straight line. If you use INTERCEPT on this data, it looks at the pattern and tells you where that line would have started at "week 0," even though you never actually measured week 0. Here, the answer is 1, meaning the line predicts the sprout was 1 centimeter tall right before your first measurement. Real measurements are rarely this tidy, but INTERCEPT still finds the best straight line even through messy, scattered data.

How to use it

1. Open Microsoft Excel and type your two lists of numbers into two columns. For the plant example, put the weeks in cells A2 through A5, and the matching heights in B2 through B5.
2. Click on an empty cell where you want the answer to appear.
3. Type =INTERCEPT( and then click and drag to select your height numbers, B2:B5. These go first, because INTERCEPT always wants the "y" values, the ones you measured, before the "x" values.
4. Type a comma, then click and drag to select your week numbers, A2:A5.
5. Type the closing parenthesis and press Enter, so the full formula reads =INTERCEPT(B2:B5, A2:A5).
6. Excel instantly calculates the answer and shows it in that cell. For the plant data above, it shows 1.

Fun facts

• INTERCEPT works by finding what statisticians call the "line of best fit," the one straight line that sits as close as possible to every point at once. That is the exact same math behind Linear Regression.
• INTERCEPT has a partner function called SLOPE, which finds how steep that same best fit line is. Most people use the two together to describe a trend completely: SLOPE for how fast something is changing, and INTERCEPT for where it started.
• This kind of prediction shows up far beyond science class. Store owners use it to estimate future sales from past trends, and coaches use it to guess how much faster an athlete might get with more practice.