RIDGE_REG
Ridge regression adds an L2 penalty to ordinary least squares to stabilize coefficient estimates when features are noisy or correlated. It is a common regularized baseline for continuous prediction problems.
The model addresses multicollinearity by minimizing the penalized residual sum of squares:
\min_{w} ||y - Xw||^2_2 + \alpha ||w||^2_2
where \alpha \ge 0 is the complexity parameter controlling the amount of shrinkage.
This wrapper accepts tabular feature data with rows as samples and columns as features, plus a numeric target supplied as a single row or single column. It returns the training R^2 together with fitted predictions, residuals, and learned coefficient arrays.
Excel Usage
=RIDGE_REG(data, target, alpha, ridge_solver, fit_intercept, tol, random_state)data(list[list], required): 2D array of numeric feature data with rows as samples and columns as features.target(list[list], required): Numeric target values as a single row, single column, or scalar when only one sample is present.alpha(float, optional, default: 1): L2 regularization strength applied to the regression model.ridge_solver(str, optional, default: “auto”): Linear algebra solver used to fit the ridge model.fit_intercept(bool, optional, default: true): Whether to include an intercept term in the linear model.tol(float, optional, default: 0.0001): Convergence tolerance for iterative solvers.random_state(int, optional, default: null): Integer seed for stochastic solvers. Leave blank for the estimator default.
Returns (dict): Excel data type containing training R^2, predictions, residuals, and fitted coefficient arrays.
Example 1: Fit ridge regression on a two-feature linear trend
Inputs:
| data | target | alpha | ridge_solver | fit_intercept | tol | random_state | |
|---|---|---|---|---|---|---|---|
| 0 | 0 | 1 | 1 | auto | true | 0.0001 | 0 |
| 1 | 0 | 3 | |||||
| 0 | 1 | 4 | |||||
| 1 | 1 | 6 | |||||
| 2 | 1 | 8 | |||||
| 2 | 2 | 11 |
Excel formula:
=RIDGE_REG({0,0;1,0;0,1;1,1;2,1;2,2}, {1;3;4;6;8;11}, 1, "auto", TRUE, 0.0001, 0)Expected output:
{"type":"Double","basicValue":0.971641,"properties":{"training_r2":{"type":"Double","basicValue":0.971641},"mean_squared_error":{"type":"Double","basicValue":0.309584},"sample_count":{"type":"Double","basicValue":6},"feature_count":{"type":"Double","basicValue":2},"predictions":{"type":"Array","elements":[[{"type":"Double","basicValue":1.71429}],[{"type":"Double","basicValue":3.6044}],[{"type":"Double","basicValue":3.98901}],[{"type":"Double","basicValue":5.87912}],[{"type":"Double","basicValue":7.76923}],[{"type":"Double","basicValue":10.044}]]},"residuals":{"type":"Array","elements":[[{"type":"Double","basicValue":-0.714286}],[{"type":"Double","basicValue":-0.604396}],[{"type":"Double","basicValue":0.010989}],[{"type":"Double","basicValue":0.120879}],[{"type":"Double","basicValue":0.230769}],[{"type":"Double","basicValue":0.956044}]]},"coefficients":{"type":"Array","elements":[[{"type":"Double","basicValue":1.89011},{"type":"Double","basicValue":2.27473}]]},"intercepts":{"type":"Array","elements":[[{"type":"Double","basicValue":1.71429}]]}}}
Example 2: Flatten a single-row numeric target range for ridge regression
Inputs:
| data | target | alpha | ridge_solver | fit_intercept | tol | random_state | |||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1 | 3 | 5 | 7 | 9 | 11 | 0.5 | auto | true | 0.0001 | 0 |
| 1 | |||||||||||
| 2 | |||||||||||
| 3 | |||||||||||
| 4 | |||||||||||
| 5 |
Excel formula:
=RIDGE_REG({0;1;2;3;4;5}, {1,3,5,7,9,11}, 0.5, "auto", TRUE, 0.0001, 0)Expected output:
{"type":"Double","basicValue":0.999228,"properties":{"training_r2":{"type":"Double","basicValue":0.999228},"mean_squared_error":{"type":"Double","basicValue":0.00900206},"sample_count":{"type":"Double","basicValue":6},"feature_count":{"type":"Double","basicValue":1},"predictions":{"type":"Array","elements":[[{"type":"Double","basicValue":1.13889}],[{"type":"Double","basicValue":3.08333}],[{"type":"Double","basicValue":5.02778}],[{"type":"Double","basicValue":6.97222}],[{"type":"Double","basicValue":8.91667}],[{"type":"Double","basicValue":10.8611}]]},"residuals":{"type":"Array","elements":[[{"type":"Double","basicValue":-0.138889}],[{"type":"Double","basicValue":-0.0833333}],[{"type":"Double","basicValue":-0.0277778}],[{"type":"Double","basicValue":0.0277778}],[{"type":"Double","basicValue":0.0833333}],[{"type":"Double","basicValue":0.138889}]]},"coefficients":{"type":"Array","elements":[[{"type":"Double","basicValue":1.94444}]]},"intercepts":{"type":"Array","elements":[[{"type":"Double","basicValue":1.13889}]]}}}
Example 3: Fit a no-intercept ridge model on a two-feature plane
Inputs:
| data | target | alpha | ridge_solver | fit_intercept | tol | random_state | |
|---|---|---|---|---|---|---|---|
| 1 | 0 | 2 | 0.2 | svd | false | 0.0001 | 0 |
| 0 | 1 | 3 | |||||
| 1 | 1 | 5 | |||||
| 2 | 1 | 7 | |||||
| 1 | 2 | 8 | |||||
| 2 | 2 | 10 |
Excel formula:
=RIDGE_REG({1,0;0,1;1,1;2,1;1,2;2,2}, {2;3;5;7;8;10}, 0.2, "svd", FALSE, 0.0001, 0)Expected output:
{"type":"Double","basicValue":0.9993,"properties":{"training_r2":{"type":"Double","basicValue":0.9993},"mean_squared_error":{"type":"Double","basicValue":0.00546198},"sample_count":{"type":"Double","basicValue":6},"feature_count":{"type":"Double","basicValue":2},"predictions":{"type":"Array","elements":[[{"type":"Double","basicValue":2.0207}],[{"type":"Double","basicValue":2.92979}],[{"type":"Double","basicValue":4.9505}],[{"type":"Double","basicValue":6.9712}],[{"type":"Double","basicValue":7.88029}],[{"type":"Double","basicValue":9.90099}]]},"residuals":{"type":"Array","elements":[[{"type":"Double","basicValue":-0.0207021}],[{"type":"Double","basicValue":0.070207}],[{"type":"Double","basicValue":0.049505}],[{"type":"Double","basicValue":0.0288029}],[{"type":"Double","basicValue":0.119712}],[{"type":"Double","basicValue":0.0990099}]]},"coefficients":{"type":"Array","elements":[[{"type":"Double","basicValue":2.0207},{"type":"Double","basicValue":2.92979}]]},"intercepts":{"type":"Array","elements":[[{"type":"Double","basicValue":0}]]}}}
Example 4: Fit ridge regression on correlated features with the LSQR solver
Inputs:
| data | target | alpha | ridge_solver | fit_intercept | tol | random_state | |
|---|---|---|---|---|---|---|---|
| 0 | 0.1 | 1 | 0.3 | lsqr | true | 0.0001 | 0 |
| 1 | 0.9 | 3.1 | |||||
| 2 | 2.1 | 5.8 | |||||
| 3 | 2.9 | 7.9 | |||||
| 4 | 4.1 | 10.2 | |||||
| 5 | 4.9 | 12.1 |
Excel formula:
=RIDGE_REG({0,0.1;1,0.9;2,2.1;3,2.9;4,4.1;5,4.9}, {1;3.1;5.8;7.9;10.2;12.1}, 0.3, "lsqr", TRUE, 0.0001, 0)Expected output:
{"type":"Double","basicValue":0.998837,"properties":{"training_r2":{"type":"Double","basicValue":0.998837},"mean_squared_error":{"type":"Double","basicValue":0.0172674},"sample_count":{"type":"Double","basicValue":6},"feature_count":{"type":"Double","basicValue":2},"predictions":{"type":"Array","elements":[[{"type":"Double","basicValue":1.1603}],[{"type":"Double","basicValue":3.18951}],[{"type":"Double","basicValue":5.66873}],[{"type":"Double","basicValue":7.69794}],[{"type":"Double","basicValue":10.1772}],[{"type":"Double","basicValue":12.2064}]]},"residuals":{"type":"Array","elements":[[{"type":"Double","basicValue":-0.1603}],[{"type":"Double","basicValue":-0.0895065}],[{"type":"Double","basicValue":0.13127}],[{"type":"Double","basicValue":0.202064}],[{"type":"Double","basicValue":0.0228398}],[{"type":"Double","basicValue":-0.106366}]]},"coefficients":{"type":"Array","elements":[[{"type":"Double","basicValue":1.12917},{"type":"Double","basicValue":1.12504}]]},"intercepts":{"type":"Array","elements":[[{"type":"Double","basicValue":1.0478}]]}}}
Python Code
import numpy as np
from sklearn.linear_model import Ridge as SklearnRidge
def ridge_reg(data, target, alpha=1, ridge_solver='auto', fit_intercept=True, tol=0.0001, random_state=None):
"""
Fit a ridge regression model and return training predictions.
See: https://scikit-learn.org/stable/modules/generated/sklearn.linear_model.Ridge.html
This example function is provided as-is without any representation of accuracy.
Args:
data (list[list]): 2D array of numeric feature data with rows as samples and columns as features.
target (list[list]): Numeric target values as a single row, single column, or scalar when only one sample is present.
alpha (float, optional): L2 regularization strength applied to the regression model. Default is 1.
ridge_solver (str, optional): Linear algebra solver used to fit the ridge model. Valid options: Auto, SVD, LSQR, SAG, SAGA. Default is 'auto'.
fit_intercept (bool, optional): Whether to include an intercept term in the linear model. Default is True.
tol (float, optional): Convergence tolerance for iterative solvers. Default is 0.0001.
random_state (int, optional): Integer seed for stochastic solvers. Leave blank for the estimator default. Default is None.
Returns:
dict: Excel data type containing training $R^2$, predictions, residuals, and fitted coefficient arrays.
"""
def py(value):
return value.item() if isinstance(value, np.generic) else value
def cell(value):
value = py(value)
if isinstance(value, bool):
return {"type": "Boolean", "basicValue": bool(value)}
if isinstance(value, (int, float)) and not isinstance(value, bool):
return {"type": "Double", "basicValue": float(value)}
return {"type": "String", "basicValue": str(value)}
def col(values):
return [[cell(value)] for value in values]
def mat(values):
return [[cell(value) for value in row] for row in values]
def parse_data(value):
value = [[value]] if not isinstance(value, list) else value
if not isinstance(value, list) or not value or not all(isinstance(row, list) and row for row in value):
return None, "Error: data must be a non-empty 2D list"
if len({len(row) for row in value}) != 1:
return None, "Error: data must be a rectangular 2D list"
data_np = np.array(value, dtype=float)
if data_np.ndim != 2 or data_np.size == 0:
return None, "Error: data must be a non-empty 2D list"
if not np.isfinite(data_np).all():
return None, "Error: data must contain only finite numeric values"
return data_np, None
def parse_target(value, sample_count):
if not isinstance(value, list):
labels = [value]
elif not value:
return None, "Error: target must be non-empty"
elif all(not isinstance(item, list) for item in value):
labels = value
elif len(value) == 1:
labels = value[0]
elif all(isinstance(row, list) and len(row) == 1 for row in value):
labels = [row[0] for row in value]
else:
return None, "Error: target must be a single row or column"
if len(labels) != sample_count:
return None, "Error: target length must match sample count"
parsed = []
for item in labels:
item = py(item)
if isinstance(item, bool) or not isinstance(item, (int, float)):
return None, "Error: target values must be finite numeric scalars"
if not np.isfinite(float(item)):
return None, "Error: target values must be finite numeric scalars"
parsed.append(float(item))
return np.array(parsed, dtype=float), None
def flat_float_list(values):
return [float(py(item)) for item in np.asarray(values).reshape(-1).tolist()]
try:
data_np, error = parse_data(data)
if error:
return error
target_np, error = parse_target(target, data_np.shape[0])
if error:
return error
solver_value = str(ridge_solver).strip().lower()
if solver_value not in {"auto", "svd", "lsqr", "sag", "saga"}:
return "Error: ridge_solver must be 'auto', 'svd', 'lsqr', 'sag', or 'saga'"
if float(alpha) < 0:
return "Error: alpha must be non-negative"
if float(tol) <= 0:
return "Error: tol must be greater than 0"
fitted = SklearnRidge(
alpha=float(alpha),
solver=solver_value,
fit_intercept=bool(fit_intercept),
tol=float(tol),
random_state=None if random_state in (None, "") else int(random_state)
).fit(data_np, target_np)
prediction_array = np.asarray(fitted.predict(data_np), dtype=float)
residual_array = target_np - prediction_array
predictions = flat_float_list(prediction_array)
residuals = flat_float_list(residual_array)
training_r2 = float(fitted.score(data_np, target_np))
mse = float(np.mean(np.square(residual_array)))
return {
"type": "Double",
"basicValue": training_r2,
"properties": {
"training_r2": {"type": "Double", "basicValue": training_r2},
"mean_squared_error": {"type": "Double", "basicValue": mse},
"sample_count": {"type": "Double", "basicValue": float(data_np.shape[0])},
"feature_count": {"type": "Double", "basicValue": float(data_np.shape[1])},
"predictions": {"type": "Array", "elements": col(predictions)},
"residuals": {"type": "Array", "elements": col(residuals)},
"coefficients": {"type": "Array", "elements": mat(np.atleast_2d(fitted.coef_).tolist())},
"intercepts": {"type": "Array", "elements": col(np.atleast_1d(fitted.intercept_).tolist())}
}
}
except Exception as e:
return f"Error: {str(e)}"