How to Use the COVARIANCE.S and COVARIANCE.P Functions in Google Sheets to Measure Joint Variability

Understanding Joint Variability in Statistical Modeling

When analysing financial portfolios, experimental telemetry, or sales metrics across two interrelated variables, analysts need to determine whether the two data series move in tandem or in opposite directions. In statistical theory, covariance measures the directional relationship between the returns or values of two random variables:

• Positive Covariance: Indicates that both variables tend to move in the same direction simultaneously (e.g., as advertising spend rises, monthly sales revenue increases).
• Negative Covariance: Indicates that both variables tend to move in inverse directions (e.g., as outdoor temperature rises, residential heating costs decline).
• Zero Covariance: Demonstrates that the two variables possess no linear co-movement or dependency.

Google Sheets provides two distinct, dedicated statistical functions to evaluate covariance: COVARIANCE.S (sample covariance) and COVARIANCE.P (population covariance). Selecting the appropriate function ensures your mathematical formulas align with standard statistical error correction.

COVARIANCE.S vs COVARIANCE.P: Key Differences

The primary distinction between the two functions lies in whether your dataset represents an entire exhaustive population or merely a representative sample drawn from a larger group:

1. Sample Covariance: COVARIANCE.S

In almost all practical business, financial, and scientific scenarios, you work with sample observations rather than every possible historical or future data point. The sample formula divides the sum of paired deviation products by n - 1 (known as Bessel’s correction), where n is the total number of paired observations:

Cov(X, Y) = Σ [(Xi - X̄) * (Yi - Ȳ)] / (n - 1)

Bessel’s correction corrects the inherent downward bias that occurs when estimating population variability from a finite sample subset.

2. Population Covariance: COVARIANCE.P

When your dataset comprises every single existing member or event in an entire closed system (such as all 30 teams in a sports league, or all 100 sensors in a single wind turbine assembly), sample correction is unnecessary. The population formula divides the sum of products by the total population count N:

Cov(X, Y) = Σ [(Xi - μx) * (Yi - μy)] / N

Function Syntax and Argument Structure

Both functions share identical argument requirements in Google Sheets:

=COVARIANCE.S(data_y, data_x)
=COVARIANCE.P(data_y, data_x)

Parameter	Required / Optional	Description and Validation Rules
data_y	Required	The range, array, or cell reference containing the dependent numerical variable series.
data_x	Required	The range, array, or cell reference containing the independent numerical variable series.

Critical Operational Rules:

1. Both ranges must contain an identical count of data points. If data_y contains 20 rows while data_x contains 18 rows, Google Sheets returns a #N/A argument mismatch error.
2. Cells containing text, boolean flags (TRUE / FALSE), or empty blanks are ignored. However, if a cell contains text that cannot be parsed as a number, verify that paired entries remain balanced.
3. Both ranges must contain at least two valid numeric pairs for COVARIANCE.S, or at least one pair for COVARIANCE.P; otherwise, a #DIV/0! error occurs.

Step-by-Step Practical Examples in Google Sheets

Example 1: Calculating Sample Covariance for Marketing ROI

Suppose you are evaluating monthly advertising expenditure against closed customer acquisitions across eight regional branches:

Row	Column A (Month)	Column B (Ad Spend in £)	Column C (New Customers)
2	January	12,500	310
3	February	14,200	380
4	March	11,000	290
5	April	16,800	420
6	May	15,400	395
7	June	13,100	330
8	July	17,500	450
9	August	18,200	490

Because these eight months represent a representative operational sample, apply COVARIANCE.S in cell E2:

=COVARIANCE.S(C2:C9, B2:B9)

The resulting positive value confirms that increased advertising spend corresponds with higher customer acquisition volumes.

Example 2: Calculating Population Covariance for Asset Returns

In modern portfolio theory, finance teams measure the joint volatility between two portfolio assets over an entire fixed fiscal year consisting of twelve complete accounting months:

=COVARIANCE.P(B2:B13, C2:C13)

If the result yields a negative covariance (e.g., -45.2), the two assets provide natural portfolio diversification by offsetting each other’s cyclical downturns.

Covariance vs Correlation: Understanding the Limitations

While covariance indicates the direction of a relationship (positive or negative), its numerical magnitude depends entirely on the units of measurement used for the underlying variables. For example, measuring advertising expenditure in pennies instead of pounds scales the resulting covariance figure by 100, even though the underlying economic correlation remains identical.

To evaluate the strength and standardised degree of linear association between -1 and +1, compute correlation alongside covariance:

=CORREL(C2:C9, B2:B9)

Alternatively, calculate correlation mathematically by dividing covariance by the product of both variables’ standard deviations:

=COVARIANCE.S(C2:C9, B2:B9) / (STDEV.S(C2:C9) * STDEV.S(B2:B9))

Troubleshooting Common Formula Errors

When building financial models with covariance functions, monitor these recurring issues:

• #N/A Error: Occurs when the dimensions of the two array arguments do not match. Verify that both column ranges start and terminate on the same row indices (e.g., B2:B20 paired with C2:C20).
• #DIV/0! Error: Triggers when one or both input ranges contain fewer than two numeric entries, or if all values in a range are identical (yielding zero variance).
• #VALUE! Error: Occurs if an array formula passes non-numeric data structures or corrupted matrix arguments directly into the calculation core.