Skip to content
Tutorials Center
My learning

Topics

Lesson 38 of 93 ยท Statistics and Math

Correlation, Regression and Forecasting

Measure relationships and predict values in Excel with CORREL, SLOPE, INTERCEPT, RSQ, LINEST, FORECAST.LINEAR, TREND, the Forecast Sheet and moving averages.

"Do sales go up when we spend more on ads?" "If this trend continues, what will we sell in month 13?" These are questions about the relationship between two sets of numbers, and Excel can answer them without any add-in. Correlation measures how strongly two columns move together. Regression finds the straight line that best fits the points. Forecasting uses that line to estimate values you have not observed yet.

A word of caution before the functions: they always return a number, even when a straight line is a poor model, and a strong correlation never proves that one thing causes the other. In this lesson you will compute everything, then look at what the numbers can and cannot tell you. Excel cannot run on this site, so the TryIts are stand-ins: JavaScript functions that implement the textbook formulas behind the Excel functions, and NumPy in real Python.

From relationship to prediction

Correlate How strongly do two columns move together?
Fit a line Find slope and intercept.
Forecast Predict y for a new x.
1 / 2

Correlation: CORREL

CORREL(array1, array2) returns the Pearson correlation coefficient r, a number between -1 and 1:

  • near 1: when one column rises, the other rises in a nearly straight-line pattern
  • near -1: when one rises, the other falls
  • near 0: no straight-line relationship (there may still be a curved one)

The two ranges must have the same size and be paired row by row. Rows where either value is text or blank are skipped in pairs for a range reference. PEARSON is the same function under an older name. COVARIANCE.S is the related unscaled measure.

The line: SLOPE, INTERCEPT, RSQ

Simple linear regression fits y = slope * x + intercept to minimize the squared vertical distances to the points (least squares). The functions take the y values first, then the x values, which is the opposite of what many people expect:

  • SLOPE(known_ys, known_xs): how much y changes per one unit of x
  • INTERCEPT(known_ys, known_xs): the predicted y when x is 0 (it is only meaningful if x = 0 makes sense)
  • RSQ(known_ys, known_xs): R squared, the share of the variation in y explained by the line; it equals CORREL squared
  • STEYX(known_ys, known_xs): the typical size of the prediction error

An R squared of 0.9 means 90% of the variation in y is accounted for by x. An R squared of 0.05 means the line is nearly useless for prediction, whatever its slope.

LINEST: everything at once

LINEST(known_ys, [known_xs], [const], [stats]) returns the regression coefficients as an array. With one x column it gives the slope in the first cell and the intercept in the second. With several x columns (multiple regression) it returns one coefficient per column, in reverse order, followed by the intercept. With stats set to TRUE it adds a table with standard errors, R squared, the F statistic and degrees of freedom. In modern Excel the result spills; in old versions you had to select a block and confirm with Ctrl+Shift+Enter (see Dynamic Arrays and Spilling). Use INDEX(LINEST(...), 1, 2) to pick one value. For one x column, SLOPE and INTERCEPT are simpler.

Forecasting: FORECAST.LINEAR and TREND

FORECAST.LINEAR(x, known_ys, known_xs) predicts y for a new x using the regression line. (FORECAST is the older identical name.) TREND(known_ys, [known_xs], [new_xs]) does the same for many new x values at once and returns an array. There is also GROWTH for exponential curves, useful for compounding growth.

For time series, the x values are usually period numbers (1, 2, 3, ...) or dates. Dates work because they are numbers, but period numbers make the slope easier to read: "per month".

Extrapolation is risky. A line fitted to months 1 to 12 says nothing reliable about month 36. Seasons, capacity limits and market changes all break straight lines.

The Forecast Sheet and FORECAST.ETS

On the Data tab, Forecast Sheet creates a new sheet with a table and chart that projects a time series, with a confidence band. It uses FORECAST.ETS, an exponential-smoothing method that can pick up seasonality (a repeating yearly pattern), unlike the straight-line functions. It needs a timeline with a consistent step, such as monthly dates. The window on screen is a wizard; the functions behind it (FORECAST.ETS, FORECAST.ETS.CONFINT, FORECAST.ETS.SEASONALITY) are available if you want to build the same thing in cells. Forecast Sheet is available in Excel for Windows, Mac and the web (it is not in the oldest versions).

Run it: regression formulas in JavaScript

The stand-in below implements the least-squares formulas. The data are monthly ad spend (thousands) and units sold. You can compare the results with what =CORREL(B2:B9, A2:A9), =SLOPE(B2:B9, A2:A9) and the others give in Excel.

JS CORREL, SLOPE, INTERCEPT, RSQ and FORECAST.LINEAR

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The last two lines are the important ones. A correlation near zero does not mean "unrelated": a perfect U-shaped relationship can have almost no linear correlation. Always plot the data first; see Creating Your First Chart and use a scatter chart with a trendline.

Time series: trend and moving averages

A moving average smooths noise by averaging the last k values. In a sheet, the three-month average in row 5 is =AVERAGE(B3:B5), copied down. It lags behind real changes (the price of smoothness), and the first k - 1 rows have no value. A chart trendline of type "Moving Average" does the same inside a chart.

To forecast a series with a trend, fit a line against the period number, then add a seasonal adjustment if needed. A simple way: compute the average level for each month across years, divide each month's average by the overall average, and multiply the trend forecast by that ratio. FORECAST.ETS automates this.

Run it: a trend, a moving average and the Python version

JS Moving average and trend forecast in JavaScript

Press Run (or Ctrl + Enter) to see the console output here.

No output.

NumPy does regression too, and np.linalg.lstsq is the equivalent of LINEST with several x columns. This runs in real Python:

Python NumPy stand-in for LINEST and CORREL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

When columns such as ad spend and price move together, the individual coefficients of a multiple regression become unstable. That problem is called multicollinearity; check CORREL between your x columns before trusting each coefficient.

What to remember when you report results

  1. Plot first. One outlier can create or destroy a correlation.
  2. Correlation is not causation. Ice cream sales and sunburn both rise in summer.
  3. R squared is about fit, not truth. A high value can come from a coincidence in a short series.
  4. Stay inside the data. Interpolating is much safer than extrapolating.
  5. Say how you did it. "Linear trend on months 1-12" in a note beside the forecast avoids arguments later.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In SLOPE(known_ys, known_xs), which argument comes first?

    Excel's regression functions take the y values (the thing you predict) first and the x values second.
  2. 2. CORREL returns -0.95. What does that mean?

    The sign gives the direction and the size gives the strength. A value near -1 means a strong negative linear relationship, but it says nothing about cause.
  3. 3. What does RSQ equal?

    R squared is the square of the correlation coefficient. It is the share of y's variation explained by the line.
  4. 4. Why is forecasting month 36 from a line fitted to months 1 to 12 risky?

    The function will happily compute it, but nothing in the data supports the straight line continuing that far.
  5. 5. Which tool can detect a repeating yearly seasonal pattern when forecasting?

    Forecast Sheet uses FORECAST.ETS, an exponential-smoothing method that can model seasonality, while the linear functions fit one straight line.

Summary

  • CORREL measures straight-line association from -1 to 1; it does not prove cause and misses curved patterns.
  • SLOPE, INTERCEPT and RSQ take y first, then x; LINEST returns all coefficients (in reverse order for several x columns).
  • FORECAST.LINEAR predicts one value and TREND predicts many; stay close to your data.
  • Moving averages smooth noise but lag; the Forecast Sheet and FORECAST.ETS handle seasonality.
  • NumPy (polyfit, lstsq, corrcoef) gives the same answers in Python.

Next: Random Numbers and Simple Simulations.