Statistical Analysis and Regression in LibreOffice Calc

LibreOffice Calc provides a robust suite of built-in features for descriptive statistics, hypothesis testing, and regression modeling. Users can perform advanced statistical workflows using the dedicated Data Statistics GUI tool, native spreadsheet functions, or automated trendlines embedded within charts. This guide details the available analysis tools, regression functions, and modeling capabilities included natively in LibreOffice Calc.

The Data Statistics Toolset

The Data Statistics module provides automated analysis dialogs similar to the Analysis ToolPak in other spreadsheet software. It can be accessed via Data > Statistics in the top menu bar.

• Descriptive Statistics: Generates summary tables containing mean, standard error, median, mode, standard deviation, sample variance, kurtosis, skewness, range, minimum, maximum, sum, and count.
• Regression Analysis: Performs linear, logarithmic, power, or polynomial regression on multiple independent variables, returning coefficients, residuals, standard errors, \(R^2\), and \(F\)-test statistics.
• Analysis of Variance (ANOVA): Supports Single-Factor and Two-Factor ANOVA testing to compare means across multiple groups.
• Correlation and Covariance: Generates correlation and covariance matrix tables to measure the degree of linear association between data series.
• Exponential Smoothing and Moving Average: Calculates smoothed values and forecasts time-series data while reducing short-term volatility.
• Hypothesis Testing (t-Test, F-Test, Z-Test, Chi-Square): Evaluates differences between population variances and means for paired or independent samples.
• Sampling: Generates periodic or random data samples from a larger dataset.

Core Regression Functions

For formula-based analysis, Calc includes array functions and point-estimate functions to model linear and non-linear relationships directly in cells.

Linear and Exponential Array Formulas

• LINEST(known_y's, known_x's, linear_type, stats): Calculates linear regression using the least squares method. When entered as an array formula, it outputs slope, y-intercept, standard errors, coefficient of determination (\(R^2\)), standard error of the estimate, \(F\)-statistic, degrees of freedom, regression sum of squares, and residual sum of squares.
• LOGEST(known_y's, known_x's, function_type, stats): Calculates an exponential curve of the form \(y = b \cdot m^x\) and outputs parameter estimates and regression statistics.

Individual Linear Parameters

• SLOPE(known_y's, known_x's): Returns the slope of the linear regression line.
• INTERCEPT(known_y's, known_x's): Calculates the point where the linear regression line intersects the y-axis.
• RSQ(known_y's, known_x's): Computes the square of the Pearson correlation coefficient (\(R^2\)).
• STEYX(known_y's, known_x's): Returns the standard error of the predicted y-value for each x in the regression.

Forecasting and Prediction

• FORECAST / FORECAST.LINEAR(x, known_y's, known_x's): Predicts a future value along a linear trend.
• TREND(known_y's, known_x's, new_x's, linear_type): Fits a linear trend and computes predicted y-values for existing or new x-values.
• GROWTH(known_y's, known_x's, new_x's, function_type): Calculates predicted exponential growth values.

General Statistical Functions

Calc includes standard functions for distributions, dispersion, and central tendency:

• Central Tendency: AVERAGE, MEDIAN, MODE.SNGL, MODE.MULT, GEOMEAN, HARMEAN.
• Dispersion: STDEV.S (sample standard deviation), STDEV.P (population standard deviation), VAR.S (sample variance), VAR.P (population variance), DEVSQ (sum of squared deviations).
• Correlation: CORREL and PEARSON for Pearson product-moment correlation coefficients; COVARIANCE.S and COVARIANCE.P for covariance.
• Distributions: NORM.DIST, NORM.INV, T.DIST, T.INV, F.DIST, F.INV, CHISQ.DIST, POISSON.DIST, and BINOM.DIST for calculating probabilities and critical values.

Chart-Based Regression Trendlines

When plotting data in an XY (Scatter) or Line chart, Calc can fit trendlines directly to the visual data series.

Supported trendline types include: * Linear: \(y = mx + b\) * Logarithmic: \(y = a \cdot \ln(x) + b\) * Exponential: \(y = a \cdot e^{bx}\) * Power: \(y = a \cdot x^b\) * Polynomial: Fits polynomial degrees from 2 to 6. * Moving Average: Smooths out variations across a user-defined period.

Each trendline can be configured to display its mathematical equation and the coefficient of determination (\(R^2\)) directly on the chart canvas.