Sheets

Stats functions adapted to the conventions of Google Sheets.

statwrap.sheets.average(*args)

Computes the arithmetic mean, or AVERAGE().

\[\frac{1}{n} \sum_{i=1}^{n} x_i\]

Parameters:

args (array_like or numeric scalars) – Input data. This can be a single array-like object or individual numbers. Both average([1,2]) and average(1,2) are valid.

Returns:

The average value, or arithmetic mean, for a collection of numbers.

Return type:

float

Example

>>> average(0, 5, -8, 7, -3)
0.2

statwrap.sheets.correl(x, y)

Calculates the Pearson correlation coefficient, or CORREL().

\[\frac{\sum{(x_i - \bar{x})(y_i - \bar{y})}}{\sqrt{\sum{(x_i - \bar{x})^2} \sum{(y_i - \bar{y})^2}}}\]

Parameters:

• x (array_like) – The first input array.

• y (array_like) – The second input array.

Returns:

The correlation coefficient.

Return type:

float

Example

>>> correl([0,1,1], [2,-9,2])
-0.5

statwrap.sheets.linest(y, x, verbose=False)

Estimates a linear regression, akin to LINEST().

This function performs a simple OLS (Ordinary Least Squares) regression on the provided input data to estimate the coefficients of a linear model. It returns a Hyperplane object representing the estimated linear model in the general case, or a RegressionLine object in the case of univariate regression (i.e., when there’s only one independent variable). These objects can be used to compute the predicted values of the dependent variable. If verbose is set to True, it also returns a summary of the regression analysis.

Parameters:

• y (array-like) – The dependent variable values. Should be a 1-dimensional array or list.

• x (array-like) – The independent variable values. Should be a list or a 2-dimensional array where each column represents a different variable.

• verbose (bool, optional) – If True, returns a detailed summary of the regression analysis along with the model object. Default is False.

Returns:

Always returns a RegressionLine representing the estimated linear model. The coefficients of the model are stored in the coefficients attribute of the returned object, and the model can be called as a function to compute predicted values. If verbose is True, a tuple (RegressionLine, summary) is returned where summary is the regression output from statsmodels.

Return type:

RegressionLine or tuple

Examples

>>> y = [1, 2, 3, 4, 5]
>>> x = [3, 4, 5, 6, 7]
>>> model = linest(y, x)
>>> model.coefficients
array([-2., 1.])
>>> model(10)  # prediction
8
>>> model_verbose = linest(y, x, verbose=True)
>>> model_verbose[1]  # regression summary

statwrap.sheets.normdist(x, mean=0, standard_deviation=1, cumulative=True)

Returns the value of the normal probability density function or cumulative distribution function. This mimics NORMDIST().

Parameters:

• x (float or array-like) – The point(s) at which to evaluate the distribution.

• mean (float, optional) – The mean of the Normal distribution. Default is 0.

• standard_deviation (float, optional) – The standard deviation of the Normal distribution. Default is 1.

• cumulative (bool, optional) – Whether to compute the cumulative distribution function (CDF) or the probability density function (PDF). Default is True (CDF).

Returns:

The value of the Normal distribution at x. If cumulative is True, returns the CDF value; otherwise, returns the PDF value.

Return type:

float or array-like

Examples

>>> normdist(0, mean=0, standard_deviation=1, cumulative=True)
0.8413447460685429

>>> normdist(0, mean=0, standard_deviation=1, cumulative=False)
0.3989422804014327

>>> normdist([0,1,2])
array([0.5       , 0.84134475, 0.97724987])

statwrap.sheets.slope(y, x)

Calculate the slope of the linear regression line, mimicking SLOPE().

Parameters:

• y (array_like) – Dependent variable values.

• x (array_like) – Independent variable values.

Returns:

The slope of the best fit line. The line is fit with an intercept, equivalent to stats.linregress.

Return type:

float

Examples

>>> slope([1, 2, 3], [1, 2, 4])
0.8

statwrap.sheets.stdev(*args)

Computes the sample standard deviation, or STDEV().

Parameters:

args (array_like or numeric scalars) – Input data. This can be a single array-like object or individual numbers. Both stdev([1,2]) and stdev(1,2) are valid.

Returns:

Sample standard deviation of the input data.

Return type:

float

Examples

>>> data = [-1, 0, 1]
>>> stdev(data)
1.0

>>> stdev(-1,0,1)
1.0

statwrap.sheets.stdevp(*args)

Computes the population standard deviation, or STDEVP().

Parameters:

args (array_like or numeric scalars) – Input data. This can be a single array-like object or individual numbers. Both stdevp([1,2]) and stdevp(1,2) are valid.

Returns:

Population standard deviation of the input data.

Return type:

float

Examples

>>> data = [-1, 0, 1]
>>> stdevp(data)
0.816496580927726

>>> stdevp(-1,0,1)
0.816496580927726

statwrap.sheets.var(*args)

Computes the sample variance, or VAR().

Parameters:

args (array_like or numeric scalars) – Input data. This can be a single array-like object or individual numbers. Both var([1,2]) and var(1,2) are valid.

Returns:

Sample variance of the input data.

Return type:

float

Examples

>>> data = [-1, 0, 1]
>>> var(data)
1.0

>>> var(-1,0,1)
1.0

statwrap.sheets.varp(*args)

Computes the population variance, or VARP().

Parameters:

args (array_like or numeric scalars) – Input data. This can be a single array-like object or individual numbers. Both varp([1,2]) and varp(1,2) are valid.

Returns:

Population variance of the input data.

Return type:

float

Examples

>>> data = [-1, 0, 1]
>>> varp(data)
0.6666666666666666

>>> varp(-1,0,1)
0.6666666666666666