LibreOffice Calc · Analysis and modeling · Intermediate
Measure correlation and fit regression models
A worked Calc example to measure correlation and fit regression models, with source checks, changed inputs and recovery.
Documentation-qualified for Calc 26.2; native workflow and persistence checks pending · Editorial standards
Correlation describes association; regression fits a prediction equation. Neither establishes that changing one variable causes the other to change. Use matched observations and a residual check rather than interpreting a large coefficient in isolation.
Enter paired measurements
Use LibreOffice Calc 26.2 desktop with English standard menus on Windows/Linux and a disposable ODS file. This workflow is documentation-based; native execution and save/reopen verification remain pending.
On Measurements, enter A1:B6:
| Input X | Output Y |
|---|---|
| 1 | 3 |
| 2 | 5 |
| 3 | 8 |
| 4 | 9 |
| 5 | 10 |
Every row is one matched observation. Never sort one column independently. X mean is 3 and Y mean 7. The independently calculated sums of centered products are Sxx=10, Sxy=18 and Syy=34. These give correlation 18/sqrt(340), approximately 0.976187, and a linear fitted equation Y = 1.6 + 1.8X.
Produce the correlation matrix
Choose Data > Statistics > Correlation. Set Input Range to $Measurements.$A$2:$B$6, Results to a blank area such as D1, and Grouped By to Columns. Confirm.
The off-diagonal relationship should be about 0.976187 and the self-correlations 1. This measures positive linear association in these five pairs. It is not a statement that 97.6% of Y is explained; the regression’s R² is the squared coefficient for this one-predictor model with an intercept, approximately 0.952941.
If a column is constant, its correlation is undefined rather than zero. If the coefficient is unexpectedly negative, check record pairing and signs before deciding the real process has reversed.
Fit the numeric regression report
- Choose Data > Statistics > Regression.
- Set Independent variable(s) (X) range to
$Measurements.$A$1:$A$6and Dependent variable (Y) range to$Measurements.$B$1:$B$6. - Enable Both X and Y ranges have labels. This deliberately includes the header in both ranges; the preceding correlation fixture did not.
- Select Columns and a separate blank Results to location, such as G1, leaving ample room for a report and residual table.
- Choose Linear Regression, Confidence level 0.95, and Calculate residuals. Leave Force intercept to be zero off.
- Confirm and inspect slope 1.8, intercept 1.6 and R² approximately 0.952941.
Expected fitted Y values are 3.4, 5.2, 7.0, 8.8, 10.6; observed minus fitted residuals are −0.4, −0.2, 1.0, 0.2, −0.6. Their sum is zero to rounding precision and their squared sum is 1.6. A residual pattern can expose a model problem hidden by one overall fit statistic.
The Confidence level controls coefficient confidence intervals; it does not assert that 95% of future observations will fall within those coefficient intervals. With only five synthetic observations, avoid treating the report as substantive evidence about a real population.
Change one observation and refit
Change B6 from 10 to 12. Now the exact line is still not perfect: the slope becomes 2.2, intercept 0.8, R² about 0.983740 and correlation about 0.991837. Regenerate both analyses in clearly labeled new output areas, or verify existing output recalculation rather than assuming it.
The new predictions are 3.0, 5.2, 7.4, 9.6, 11.8 and residuals 0, −0.2, 0.6, −0.6, 0.2. The squared residual sum is 0.8. One altered endpoint materially changes the slope, a useful warning about small-data sensitivity.
Restore 10 and rerun the baseline. Save and reopen the ODS, checking the paired source range and report settings. For a visual overlay, use a numeric XY chart with appropriate numeric X data; the linked page explains the numeric-axis distinction, not a requirement to turn these inputs into dates.
Recover from bad inputs
Unequal range lengths, mismatched header choices or a shifted row invalidate the comparison. Repair them at the source. Logarithmic and power regression impose positive-value requirements for transformed inputs; do not switch models merely to improve a displayed fit. If you need multiple predictors, keep their X observations adjacent as the dialog requires, and reassess sample size and model assumptions before interpreting the extra coefficients.
Official references
Sources were captured on 20 October 2026 and reviewed from retained snapshots. All outcomes above are expected checks, not a claim of native execution.