LINREGRESS
Overview
The LINREGRESS function fits a straight line to paired data using ordinary least squares regression, then reports both fit quality and hypothesis-test statistics for the slope. It is useful when an analyst needs to quantify linear trend strength, estimate change in y per unit change in x, and test whether that trend is likely different from zero.
For observations (x_i, y_i), the fitted model is \hat{y}_i = \beta_0 + \beta_1 x_i, where \beta_1 is slope and \beta_0 is intercept. The least-squares estimates are:
\hat{\beta}_1 = \frac{\sum_{i=1}^{n}(x_i-\bar{x})(y_i-\bar{y})}{\sum_{i=1}^{n}(x_i-\bar{x})^2}, \qquad \hat{\beta}_0 = \bar{y} - \hat{\beta}_1\bar{x}
with n observations and sample means \bar{x}, \bar{y}. The reported correlation is r, and the test for H_0:\beta_1=0 uses a t-statistic:
t = \frac{\hat{\beta}_1}{SE(\hat{\beta}_1)}
from which a p-value is computed under the selected alternative hypothesis.
This implementation wraps scipy.stats.linregress from SciPy. In this tool, key inputs are x, y, and alternative (default "two-sided"; also "less" or "greater"), and outputs include slope, intercept, r-value, p-value, and standard errors.
Linear regression is widely used in business analytics, engineering, economics, and scientific research for trend analysis, calibration, and quick predictive baselines. It is especially valuable in exploratory analysis because it combines an interpretable model with uncertainty estimates, helping practitioners distinguish signal from noise before moving to more complex models.
This example function is provided as-is without any representation of accuracy.
Excel Usage
=LINREGRESS(x, y, alternative)x(list[list], required): Independent variable values.y(list[list], required): Dependent variable values.alternative(str, optional, default: “two-sided”): Defines the alternative hypothesis.
Returns (dict): Slope of the regression line. Additional statistics (intercept, r-value, etc.) are available as properties.
Example 1: Linear relationship
Inputs:
| x | y |
|---|---|
| 0 | 0 |
| 1 | 2 |
| 2 | 4 |
| 3 | 6 |
Excel formula:
=LINREGRESS({0;1;2;3}, {0;2;4;6})Expected output:
{"type":"Double","basicValue":2,"properties":{"Slope":{"type":"Double","basicValue":2},"Intercept":{"type":"Double","basicValue":0},"R-Value":{"type":"Double","basicValue":1},"P-Value":{"type":"Double","basicValue":1e-20},"Standard Error":{"type":"Double","basicValue":0},"Intercept Standard Error":{"type":"Double","basicValue":0}}}
Example 2: With noise
Inputs:
| x | y |
|---|---|
| 0 | 0.1 |
| 1 | 0.9 |
| 2 | 2.1 |
Excel formula:
=LINREGRESS({0;1;2}, {0.1;0.9;2.1})Expected output:
{"type":"Double","basicValue":1,"properties":{"Slope":{"type":"Double","basicValue":1},"Intercept":{"type":"Double","basicValue":0.0333333},"R-Value":{"type":"Double","basicValue":0.993399},"P-Value":{"type":"Double","basicValue":0.0731864},"Standard Error":{"type":"Double","basicValue":0.11547},"Intercept Standard Error":{"type":"Double","basicValue":0.149071}}}
Example 3: Alternative less
Inputs:
| x | y | alternative |
|---|---|---|
| 0 | 2 | less |
| 1 | 1 | |
| 2 | 0 |
Excel formula:
=LINREGRESS({0;1;2}, {2;1;0}, "less")Expected output:
{"type":"Double","basicValue":-1,"properties":{"Slope":{"type":"Double","basicValue":-1},"Intercept":{"type":"Double","basicValue":2},"R-Value":{"type":"Double","basicValue":-1},"P-Value":{"type":"Double","basicValue":4.50158e-11},"Standard Error":{"type":"Double","basicValue":0},"Intercept Standard Error":{"type":"Double","basicValue":0}}}
Example 4: Alternative greater with positive slope
Inputs:
| x | y | alternative |
|---|---|---|
| 1 | 1 | greater |
| 2 | 3 | |
| 3 | 5 | |
| 4 | 7 |
Excel formula:
=LINREGRESS({1;2;3;4}, {1;3;5;7}, "greater")Expected output:
{"type":"Double","basicValue":2,"properties":{"Slope":{"type":"Double","basicValue":2},"Intercept":{"type":"Double","basicValue":-1},"R-Value":{"type":"Double","basicValue":1},"P-Value":{"type":"Double","basicValue":5e-21},"Standard Error":{"type":"Double","basicValue":0},"Intercept Standard Error":{"type":"Double","basicValue":0}}}
Python Code
Show Code
import numpy as np
from scipy.stats import linregress as scipy_linregress
def linregress(x, y, alternative='two-sided'):
"""
Calculate a linear least-squares regression for two sets of measurements.
See: https://docs.scipy.org/doc/scipy/reference/generated/scipy.stats.linregress.html
This example function is provided as-is without any representation of accuracy.
Args:
x (list[list]): Independent variable values.
y (list[list]): Dependent variable values.
alternative (str, optional): Defines the alternative hypothesis. Valid options: Two-sided, Less, Greater. Default is 'two-sided'.
Returns:
dict: Slope of the regression line. Additional statistics (intercept, r-value, etc.) are available as properties.
"""
try:
def to2d(val):
return [[val]] if not isinstance(val, list) else val
x = to2d(x)
y = to2d(y)
if not isinstance(x, list) or not isinstance(y, list):
return "Error: x and y must be 2D lists."
# Flatten logic
try:
x_flat = [float(row[0]) if isinstance(row, list) else float(row) for row in x]
y_flat = [float(row[0]) if isinstance(row, list) else float(row) for row in y]
except Exception:
return "Error: x and y must contain numeric values."
if len(x_flat) != len(y_flat):
return "Error: x and y must have the same number of rows."
if len(x_flat) < 2:
return "Error: x and y must have at least two rows."
result = scipy_linregress(x_flat, y_flat, alternative=alternative)
vals = [result.slope, result.intercept, result.rvalue, result.pvalue, result.stderr, result.intercept_stderr]
# Check for nan/inf is good practice, though scipy often returns them.
# The provided python code did check.
if any(np.isnan(v) or np.isinf(v) for v in vals):
return "Error: Result contains invalid values."
return {
"type": "Double",
"basicValue": result.slope,
"properties": {
"Slope": { "type": "Double", "basicValue": result.slope },
"Intercept": { "type": "Double", "basicValue": result.intercept },
"R-Value": { "type": "Double", "basicValue": result.rvalue },
"P-Value": { "type": "Double", "basicValue": result.pvalue },
"Standard Error": { "type": "Double", "basicValue": result.stderr },
"Intercept Standard Error": { "type": "Double", "basicValue": result.intercept_stderr }
}
}
except Exception as e:
return f"Error: {str(e)}"