CORREL and LINEST Equivalents in Python

Two of Excel's statistics functions do a lot of quiet work in reporting spreadsheets. CORREL says how strongly two columns move together — ad spend and sales, temperature and energy use. LINEST fits a straight line (or a plane, with several inputs) and returns the coefficients plus a block of statistics that tells you how much to trust them. Moving these calculations to Python means knowing which pandas or NumPy call gives the same number, and knowing LINEST's quirks well enough to reproduce its output exactly — including the one that surprises everybody, its reversed coefficient order. This page is part of Excel Formula Equivalents in pandas.

Prerequisites

pip install pandas numpy scipy openpyxl

ads.xlsx holds twelve months of marketing data: TV spend and online spend (in thousands) and sales (also in thousands). The workbook also contains the spreadsheet versions of the calculations — CORREL in F1, RSQ in F2 and a full LINEST in F4:H8 — so the Python results can be checked against them.

import numpy as np
import pandas as pd

df = pd.read_excel("ads.xlsx", sheet_name="Ads", usecols="A:D")
print(df.to_string(index=False))

Step 1 — CORREL, PEARSON and RSQ

CORREL(x, y) is the Pearson correlation coefficient, and so is PEARSON — they are the same function under two names. In pandas it is Series.corr:

r = df["TV"].corr(df["Sales"])
print(f"CORREL(TV, Sales) = {r:.6f}")
print(f"RSQ(Sales, TV)    = {r ** 2:.6f}")
print(df[["TV", "Online", "Sales"]].corr().round(3))

RSQ is simply the square of that correlation: the share of the variation in sales that a straight line on TV spend alone explains. DataFrame.corr() computes every pair at once — the correlation matrix that would take a grid of CORREL formulas in Excel. Missing values are handled differently: Excel's CORREL skips pairs where either cell is text or empty; pandas skips pairs where either value is NaN, which amounts to the same thing once the columns are numeric.

method="spearman" gives a rank correlation, which Excel has no single function for. It is the better measure when the relationship is consistent but not a straight line, or when one extreme month would otherwise dominate.

Step 2 — Simple regression: SLOPE, INTERCEPT and friends

For one input, scipy.stats.linregress returns the slope, intercept, correlation and the standard error of the slope in one call — the numbers SLOPE, INTERCEPT, CORREL and the second row of a one-variable LINEST give:

from scipy.stats import linregress

fit = linregress(df["TV"], df["Sales"])
print(f"slope {fit.slope:.4f}  intercept {fit.intercept:.4f}  r {fit.rvalue:.4f}  se {fit.stderr:.4f}")

With TV spend alone, each extra thousand spent goes with about 1.87 thousand more in sales. That number turns out to understate TV's effect, which is the reason for the next step. The correlation matrix above showed TV and online spend moving slightly against each other (−0.23): in months with heavy TV spend, online spend tended to be lower. A one-variable fit cannot separate the two, so part of the online effect shows up as a smaller TV slope. Only a fit that includes both inputs can attribute sales to each.

Step 3 — Multiple regression: LINEST with several inputs

LINEST(y, x1:x2) fits sales on both spends at once. In NumPy, add a column of ones for the intercept and solve with least squares:

X = np.column_stack([np.ones(len(df)), df["TV"], df["Online"]])
y = df["Sales"].to_numpy()
coef, *_ = np.linalg.lstsq(X, y, rcond=None)
print(dict(zip(["intercept", "TV", "Online"], coef.round(4).tolist())))

Now TV is worth about 2.18 per thousand and online about 3.26, with a baseline of about 38.4 — close to the values the data was generated from, and a noticeably larger TV effect than the one-variable fit suggested.

The column of ones is what const=TRUE in LINEST means; leave it out to force the line through zero, as const=FALSE does.

Step 4 — The full LINEST statistics block

LINEST(..., TRUE, TRUE) returns five rows: coefficients, their standard errors, R² and the standard error of the estimate, the F statistic and degrees of freedom, and the regression and residual sums of squares. It also returns the coefficients in reverse order of the input columns, with the intercept last. A function that reproduces the whole block, in Excel's layout:

def linest(y, X_cols, const=True):
    Xm = np.column_stack(X_cols)
    if const:
        Xm = np.column_stack([Xm, np.ones(len(y))])
    n, k = Xm.shape
    coef, *_ = np.linalg.lstsq(Xm, y, rcond=None)
    resid = y - Xm @ coef
    ss_resid = float(resid @ resid)
    ss_total = float(((y - y.mean()) ** 2).sum()) if const else float(y @ y)
    ss_reg = ss_total - ss_resid
    df_resid = n - k
    se_y = (ss_resid / df_resid) ** 0.5
    se = np.sqrt(np.diag(np.linalg.inv(Xm.T @ Xm)) * se_y ** 2)
    k_x = k - 1 if const else k
    f = (ss_reg / k_x) / (ss_resid / df_resid)
    order = list(range(len(X_cols) - 1, -1, -1)) + ([len(X_cols)] if const else [])
    na = [np.nan] * (len(order) - 2)
    return pd.DataFrame([coef[order], se[order], [ss_reg / ss_total, se_y] + na,
                         [f, df_resid] + na, [ss_reg, ss_resid] + na])

block = linest(y, [df["TV"].to_numpy(), df["Online"].to_numpy()])
print(block.round(6).to_string(header=False, index=False))

The first row reads Online, TV, intercept — the reverse of the B:C column order in the formula. That reversal is the most common reason a script that "reads LINEST from the sheet" attaches the wrong coefficient to the wrong input. Reading the block row by row: R² of about 0.968 means the two spends explain almost all of the month-to-month variation in sales; the F statistic of about 136 on 9 residual degrees of freedom says the fit is far from chance.

Step 5 — Check against the spreadsheet

The workbook contains the same calculations as formulas. Recalculate a copy with headless LibreOffice and compare the two results cell by cell:

soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to xlsx --outdir recalculated ads.xlsx > /dev/null 2>&1
ls recalculated

from openpyxl import load_workbook

ws = load_workbook("recalculated/ads.xlsx", data_only=True)["Ads"]
sheet_block = pd.DataFrame([[c if isinstance(c, (int, float)) else np.nan for c in row]
                            for row in ws.iter_rows(min_row=4, max_row=8, min_col=6, max_col=8, values_only=True)])
print("CORREL matches:", np.isclose(ws["F1"].value, r), " RSQ matches:", np.isclose(ws["F2"].value, r ** 2))
print("LINEST block matches:", np.allclose(sheet_block, block, equal_nan=True))

Every value agrees to floating-point precision, including the #N/A padding, which the comparison treats as missing. A check like this is worth keeping in a test when a report switches from spreadsheet formulas to Python: it proves the replacement computes the same thing before anyone starts relying on it. Recalculate Excel Formulas Without Excel covers the LibreOffice step.

Step 6 — Predict: the TREND equivalent

TREND applies the fitted coefficients to new inputs. With the coefficients in natural order from Step 3, it is a matrix product:

plan = pd.DataFrame({"TV": [15.0, 25.0], "Online": [10.0, 6.0]})
X_new = np.column_stack([np.ones(len(plan)), plan["TV"], plan["Online"]])
plan["Predicted sales"] = (X_new @ coef).round(1)
print(plan.to_string(index=False))

FORECAST, TREND and SLOPE Equivalents in Python covers forecasting over time rather than from other inputs.

Performance notes

All of this is instant for reporting-sized data; lstsq handles hundreds of thousands of rows and dozens of inputs in well under a second. For more than the LINEST statistics — p-values, confidence intervals, robust standard errors, categorical inputs — use statsmodels, whose OLS(...).fit().summary() prints a complete regression table. The linest() function here earns its place when the goal is to match an existing spreadsheet number for number.

Common pitfalls

• LINEST's reversed order. The first coefficient belongs to the last input column; the intercept comes last.
• Forgetting the intercept column. lstsq without a column of ones fits a line through the origin — const=FALSE.
• Correlation is not the slope. A correlation of 0.8 says the points lie close to a line, not how steep it is.
• Collinear inputs. When two inputs move together almost perfectly, the individual coefficients become unstable even though the fit looks good; their standard errors grow, which is what the second LINEST row is for.
• Text in numeric columns. Excel silently skips text cells in CORREL; pandas may read the whole column as text. Convert with pd.to_numeric first.

Conclusion

Series.corr is CORREL and PEARSON, its square is RSQ, and DataFrame.corr() is the whole correlation grid. For regression, scipy.stats.linregress covers the one-input functions, and numpy.linalg.lstsq with a column of ones covers LINEST; the linest() function above reproduces Excel's five-row statistics block, reversed coefficient order included. Check the result against the spreadsheet once, then let Python do the work.

Frequently asked questions

What is the pandas equivalent of Excel CORREL? Series.corr: df"x".corr(df"y") returns the Pearson correlation coefficient, the same number as CORREL and PEARSON. DataFrame.corr() returns the correlation of every pair of columns.

How do I do LINEST in Python? Build an input matrix with a column of ones for the intercept and solve with numpy.linalg.lstsq. The standard errors, R², F statistic and sums of squares follow from the residuals, as in the linest() function on this page.

Why are my LINEST coefficients in the wrong order? Excel's LINEST returns coefficients in the reverse order of the input columns, with the intercept last. NumPy returns them in the order of the columns in your matrix.

How is RSQ related to CORREL? RSQ is CORREL squared. It gives the proportion of the variation in y explained by a straight-line fit on x.

Related

• Up one level: Excel Formula Equivalents in pandas — Excel functions translated function by function.
• STDEV and VAR Equivalents in pandas — the sample-versus-population question behind these statistics.
• Run a Regression with Python in Excel — the same fit inside a workbook cell.
• Add a Trendline to an Excel Chart with Python — showing the fitted line on a chart.