Topics Covered

On this page

1. 1. Correlation Analysis
2. 2. Simple Linear Regression
3. 3. Forecasting Functions
4. Adding a Trendline to a Chart
5. Best Practices for Regression Reporting
6. Key Take-aways

1. Correlation Analysis

1.1 Pearson Correlation Coefficient

1.2 Covariance

Function	Divisor	Use
=COVARIANCE.P(array1, array2)	n	Population covariance
=COVARIANCE.S(array1, array2)	n − 1	Sample covariance (unbiased)

Recall: \(r = \text{Cov}(X, Y) / (\sigma_X \sigma_Y)\). Cov is in original units; r is unit-free.

1.3 Spearman Rank Correlation

Excel has no direct Spearman function. Combine RANK.AVG with CORREL:

1. Suppose data in A2:A11 and B2:B11.
2. In C2 enter =RANK.AVG(A2, $A$2:$A$11, 1) and drag down — ranks of X (ties get average rank).
3. In D2 enter =RANK.AVG(B2, $B$2:$B$11, 1) and drag down — ranks of Y.
4. In another cell: =CORREL(C2:C11, D2:D11) — this is Spearman's \(\rho\).

1.4 Correlation Matrix (Multiple Variables)

Use Data Analysis ToolPak → Correlation:

1. Data → Data Analysis → Correlation.
2. Input Range: select all variables (multi-column block).
3. Grouped by: Columns; tick "Labels in first row" if applicable.
4. Output: choose location. Click OK.
5. Excel produces a symmetric lower-triangular matrix of pairwise correlations.

2. Simple Linear Regression

2.1 Quick Coefficients via Worksheet Functions

Function	Returns
=SLOPE(y_range, x_range)	Slope b of the regression line Y = a + bX
=INTERCEPT(y_range, x_range)	Intercept a
=RSQ(y_range, x_range)	R² (coefficient of determination)
=STEYX(y_range, x_range)	Standard error of the estimate
=FORECAST.LINEAR(x, known_y, known_x)	Predicted Y for a given X

2.2 Regression via Data Analysis ToolPak

For a full regression report (ANOVA, t-statistics, R², p-values, confidence intervals):

1. Data → Data Analysis → Regression → OK.
2. Input Y Range: the dependent variable column (with header if you tick "Labels").
3. Input X Range: the independent variable column(s). Up to 16 X variables in one regression.
4. Tick Labels if first row contains headers.
5. Tick Confidence Level (default 95 %).
6. Choose output location and tick:
   • Residuals — table of fitted Y, residuals.
   • Standardized Residuals — z-scores of residuals.
   • Residual Plots — plot residual vs each X.
   • Line Fit Plots — observed Y & fitted Y vs X.
   • Normal Probability Plots — to check residual normality.
7. Click OK.

Output Sections

• Regression Statistics: Multiple R, R Square, Adjusted R², Standard Error, Observations.
• ANOVA: df, SS, MS, F, Significance F (overall F-test p-value).
• Coefficients table: Coefficient, Std. Error, t-stat, p-value, Lower/Upper 95 % CI for each X.

3. Forecasting Functions

3.1 FORECAST.LINEAR

3.2 TREND (array)

3.3 LINEST (multiple regression)

=LINEST(known_y, known_x, [const], [stats]) returns an array of regression coefficients (and optional statistics). Useful for multivariate regression in cells.

3.4 GROWTH (exponential trend)

=GROWTH(known_y, known_x, new_x, [const]) fits \(Y = a \cdot b^X\) and predicts.

3.5 FORECAST.ETS (Exponential Smoothing for Time Series, Excel 2016+)

=FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation]) applies Holt–Winters triple-exponential smoothing for seasonal time series.

Excel also has a Forecast Sheet tool (Data → Forecast → Forecast Sheet) that automatically builds a chart and table with confidence intervals.

Adding a Trendline to a Chart

1. Build a scatter chart of X vs Y.
2. Right-click any data point → Add Trendline.
3. Choose trendline type: Linear, Polynomial (order 2, 3, …), Exponential, Logarithmic, Power, Moving Average.
4. Tick Display Equation on chart and Display R² value on chart.

The on-chart equation gives you a sanity check on SLOPE and INTERCEPT.

Best Practices for Regression Reporting

• Always inspect the scatter plot before regressing — confirm linearity.
• Look at Residual Plots — a random scatter around zero confirms model assumptions.
• R² alone is not enough — check the p-value of the slope and overall F-test.
• Avoid extrapolation far outside the observed X range.
• If R² is low, consider non-linear models or additional predictors.

Key Take-aways

• Pearson =CORREL; covariance =COVARIANCE.P/S; Spearman via RANK.AVG + CORREL.
• Slope, intercept, R² via worksheet functions or Data Analysis ToolPak → Regression for a full report.
• Forecast: =FORECAST.LINEAR (single), =TREND (multiple), =GROWTH (exponential), =FORECAST.ETS (seasonal).
• Always verify the model visually with scatter + trendline and check residuals.