R-Squared Errors: Why Excel returns #N/A in LINEST for perfectly collinear data.

In Excel’s LINEST function, an #N/A error in regression outputs—specifically within the statistics table for coefficients, standard errors, or the coefficient of determination ($R^2$)—occurs when independent variables exhibit perfect or near-perfect multicollinearity. When two or more predictor variables are exact linear combinations of each other, the design matrix lacks full column rank, making it mathematically impossible to invert the cross-product matrix ($X^T X$). Excel handles this rank deficiency by dropping redundant variables and returning #N/A.

Quick Answer

Excel returns #N/A in LINEST when predictor variables are redundant (e.g., Variable B is an exact multiple of Variable A, or rows/columns are identical). To fix this, calculate the correlation matrix across your predictors, identify variables with a correlation coefficient of $\pm 1.0$, and remove the redundant predictors from your regression range.

---

The Mechanics of Collinearity in LINEST

When you call LINEST with the fourth argument set to TRUE (requesting additional regression statistics):

=LINEST(known_y's, [known_x's], [const], [stats])

Excel attempts to compute the ordinary least squares (OLS) estimate:

$\beta = (X^T X)^{-1} X^T Y$

1. The Singularity Problem

If one predictor variable can be perfectly predicted by another (for instance, Column C is always Column B multiplied by 2, or Column D is Column A + Column B), the matrix $X^T X$ is singular:

• Its determinant is zero ($\det(X^T X) = 0$).
• It cannot be inverted using standard matrix inversion.

2. How Excel Handles Rank Deficiency

Instead of crashing or failing the entire formula with a fatal #NUM! or #VALUE!, Excel’s internal QR decomposition algorithm flags collinear variables.

• Excel forces the redundant variable’s coefficient to 0 or drops it.
• In the third row and below of the standard LINEST auxiliary statistics table, Excel outputs #N/A for the standard error of that dropped variable, as well as downstream regression metrics that rely on degrees of freedom calculations.
• If all predictors are collinear or constant, $R^2$ evaluates to #N/A because the total sum of squares cannot be partitioned.

---

Diagnosing Perfect Collinearity

To isolate which variable is breaking your regression model, follow this troubleshooting path:

[#N/A in LINEST Statistics]
          │
          ├──> Are any predictor columns constant? (e.g., all 1s or all 0s)
          │         └──> Remove constant columns from known_x's.
          │
          ├──> Is Sample Size (N) <= Number of Predictors (k)?
          │         └──> Add more data rows; observations must exceed variables.
          │
          └──> Run Correlation Matrix (CORREL)
                    └──> Find pairs where r = 1.0 or r = -1.0.
                              └──> Remove redundant variable.

Step 1: Check for Constant Columns

If a predictor column contains the same value in every single row (for example, a status code that never changes), that column is collinear with the intercept term:

1. Test each column using: =VAR.S(B2:B100)
2. If variance is 0, the column has no variation. Remove it immediately from your known_x's.

Step 2: Build a Correlation Matrix

Use the native CORREL function to evaluate dependencies between your independent variables:

=CORREL(B2:B100, C2:C100)

For a detailed guide on handling division failures during correlation checks, see Correlation vs. Causation: Troubleshooting #DIV/0! in the CORREL function..

• A correlation result of 1.000 or 1.000 confirms perfect linear dependency.
• Action: Delete one of the two collinear variables. Retaining both adds zero predictive power to your regression while destroying matrix invertibility.

Step 3: Check Common Practical Traps

1. The Dummy Variable Trap:
   When encoding categorical data (e.g., Quarter 1 through Quarter 4), users often create four binary indicator columns. If your regression includes an intercept (const = TRUE) and all four quarter columns, the sum of those columns always equals 1: $\text{Q1} + \text{Q2} + \text{Q3} + \text{Q4} = 1$ This causes perfect collinearity with the constant term.
   • Fix: Always omit one baseline category (use $k-1$ dummy variables for k categories).
2. Unit Conversion Duplication:
   Including the same measure in different units (e.g., Weight in Pounds and Weight in Kilograms) creates an exact scalar relationship: $\text{Pounds} = 2.20462 \times \text{Kilograms}$
   • Fix: Keep only one operational scale per measure.
3. Insufficient Observations:
   If you have 10 data rows and 10 predictor variables, your degrees of freedom equal zero ($df = n – k – 1$). The regression system is underdetermined, resulting in #N/A values across the standard error and fit statistics. Ensure your observation count substantially exceeds your variable count.

---

Reading the LINEST Output Table

When LINEST executes with stats = TRUE, it returns a 5-row array spanning as many columns as you have predictors (plus intercept):

Row	Col 1 ($X_k$)	Col 2 ($X_{k-1}$)	…	Last Col (Intercept)
1	Slope $m_k$	Slope $m_{k-1}$	…	Intercept b
2	$SE(m_k)$	$SE(m_{k-1})$	…	$SE(b)$
3	$R^2$	$SE(y)$	#N/A	#N/A
4	F-statistic	$df$	#N/A	#N/A
5	$SS_{reg}$	$SS_{resid}$	#N/A	#N/A

Note: Excel by design places #N/A in rows 3–5 for columns 3 and onward in the output table because those positions do not hold standard statistics. This is expected behavior, not an error.

However, if #N/A appears in Row 1 (as a slope coefficient), Row 2 (as a standard error), or in the $R^2$ position (Row 3, Col 1), you are dealing with active collinearity or insufficient data rank.

To diagnose other common lookup or statistical lookup breaks involving #N/A, consult Troubleshooting #N/A Errors in Excel: The Ultimate Lookup Diagnostic Guide. For setting up regressions correctly through alternative tools, refer to Analysis ToolPak: Fixing “Input range must be a contiguous reference” in Regressions..