What's the difference between COVARIANCE.P and COVARIANCE.S?

COVARIANCE.P divides the sum by n (population), while COVARIANCE.S divides by (n-1) for a sample. Use .P when you have the entire population of data; use .S when your data is a sample from a larger population. COVARIANCE.S yields slightly higher values because it adjusts for sampling bias.

How do I interpret covariance values?

Covariance is unbounded and scale-dependent. Positive means variables tend to increase together; negative means one increases while the other decreases. A covariance of 0 means no linear relationship. To compare across datasets, use CORREL instead, which normalizes to -1 (perfect inverse) through +1 (perfect agreement).

Can I use COVARIANCE.P with dates or non-numeric data?

No. COVARIANCE.P requires purely numeric values in both arrays. Dates stored as text or formatted text numbers cause #VALUE! errors. Convert dates to numbers first using DAY(), MONTH(), YEAR(), or serial date numbers. Always verify both ranges contain only numeric data before applying the function.

When should I use COVARIANCE.P instead of CORREL or a scatter plot?

Use COVARIANCE.P for raw, scale-dependent covariance in a population dataset. Use CORREL when you need normalized comparison across datasets. Use a scatter plot in a pivot chart or charting tool to visualize the relationship visually. For quick direction checking, COVARIANCE.P's sign is enough; for magnitude, CORREL is better.

COVARIANCE.P function

Returns the population covariance between two data sets, measuring how two variables move together across all data points.

=COVARIANCE.P(array1, array2)

Generate a COVARIANCE.P formula

Describe what you need. The generator will reach for COVARIANCE.P where COVARIANCE.P is the right tool, and tell you when it is not.

Arguments

How COVARIANCE.P reads its arguments

Argument	Required	Description
array1	Required	A range or array of numeric values; must be the same size as array2.
array2	Required	A range or array of numeric values; must be the same size as array1.

Returns

A number representing the covariance; positive indicates variables move together, negative indicates inverse movement.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Analyze correlation between vehicle mileage and maintenance cost

Vehicle	Odometer (K miles)	Service Date	Cost	Parts Cost
Van-A	45	15	250	150
Van-B	52	20	380	220
Van-C	38	10	180	100
Van-D	61	25	520	350
Van-E	29	5	120	80

=COVARIANCE.P(B2:B6, D2:D6)

Result: 1560

The positive covariance of 1560 indicates that higher mileage tends to correlate with higher maintenance costs. Each 1K-mile increase covaries with approximately 1560 units of cost change, reflecting the wear-and-tear relationship across the fleet.

2. Compare covariance between mileage and repair parts cost

Vehicle	Odometer (K miles)	Service Date	Cost	Parts Cost
Van-A	45	15	250	150
Van-B	52	20	380	220
Van-C	38	10	180	100
Van-D	61	25	520	350
Van-E	29	5	120	80

=COVARIANCE.P(B2:B6, E2:E6)

Result: 1316

Comparing mileage to parts cost alone (excluding labor) yields 1316, slightly lower than total cost covariance. This shows that parts represent most but not all of maintenance expense variation, helping identify whether cost spikes are component-driven or labor-heavy.

3. Check covariance between service date and total cost

Vehicle	Odometer (K miles)	Service Date	Cost	Parts Cost
Van-A	45	15	250	150
Van-B	52	20	380	220
Van-C	38	10	180	100
Van-D	61	25	520	350
Van-E	29	5	120	80

=COVARIANCE.P(C2:C6, D2:D6)

Result: 1000

The covariance of 1000 between service date (day of month) and cost shows a positive relationship within this sample. Later-month services tended to be more expensive, suggesting scheduling patterns or that high-mileage vehicles were deferred until later weeks.

Common errors

Which COVARIANCE.P error are you seeing?

Error	Why it happens	How to fix it
#N/A	The two arrays have different numbers of elements; COVARIANCE.P cannot pair mismatched data.	Verify both ranges have the same row count. If one range has 5 rows, the other must also have exactly 5 rows (e.g., B2:B6 paired with D2:D6, not D2:D7).
#VALUE!	One or both arrays contain text, dates stored as text, or other non-numeric values that cannot be coerced into numbers.	Extract numeric columns only. If using dates, convert them to day-of-month (DAY), serial numbers, or other numeric format. Remove any text labels or text-formatted numbers from the range.
#DIV/0!	One or both arrays are completely empty or reference a range with no numeric data, making covariance calculation impossible.	Ensure both array ranges contain at least one numeric value. Verify the range references are correct and include actual data cells, not just header rows. Check for hidden rows that might hide the data.

Tips and when to use something else

Use COVARIANCE.P for entire populations; use COVARIANCE.S if your data is a sample. COVARIANCE.S divides by (n-1) instead of n, yielding a higher result that accounts for sampling uncertainty.
Positive covariance means variables move in the same direction; negative means they move opposite. The magnitude is scale-dependent, so use CORREL for a normalized correlation (-1 to +1) for easier comparison across datasets.
COVARIANCE.P is unbounded and unit-dependent, so a covariance of 1560 between mileage and cost is meaningful only in context. The same relationship in different units (e.g., miles vs. cost in cents) produces vastly different covariance values.
Avoid comparing covariances across datasets with different scales. Instead of trying to interpret raw covariance values, use CORREL to get a normalized coefficient, or standardize your data first by dividing by STDEV.P before calculating covariance.

Related functions

CORRELCORREL returns the Pearson correlation coefficient between two data sets, ranging from -1 to 1 to indicate relationship strength.VAR.PVAR.P returns the population variance of a dataset, showing spread from the mean; use it when your data represents the entire population, not a sample.STDEV.PSTDEV.P returns the population standard deviation of a dataset, measuring how spread out values are across the entire population.AVERAGEAVERAGE calculates the arithmetic mean of numbers, ignoring text and logical values; returns a single numeric value representing the average.

Frequently asked questions

What's the difference between COVARIANCE.P and COVARIANCE.S?: COVARIANCE.P divides the sum by n (population), while COVARIANCE.S divides by (n-1) for a sample. Use .P when you have the entire population of data; use .S when your data is a sample from a larger population. COVARIANCE.S yields slightly higher values because it adjusts for sampling bias.
How do I interpret covariance values?: Covariance is unbounded and scale-dependent. Positive means variables tend to increase together; negative means one increases while the other decreases. A covariance of 0 means no linear relationship. To compare across datasets, use CORREL instead, which normalizes to -1 (perfect inverse) through +1 (perfect agreement).
Can I use COVARIANCE.P with dates or non-numeric data?: No. COVARIANCE.P requires purely numeric values in both arrays. Dates stored as text or formatted text numbers cause #VALUE! errors. Convert dates to numbers first using DAY(), MONTH(), YEAR(), or serial date numbers. Always verify both ranges contain only numeric data before applying the function.
When should I use COVARIANCE.P instead of CORREL or a scatter plot?: Use COVARIANCE.P for raw, scale-dependent covariance in a population dataset. Use CORREL when you need normalized comparison across datasets. Use a scatter plot in a pivot chart or charting tool to visualize the relationship visually. For quick direction checking, COVARIANCE.P's sign is enough; for magnitude, CORREL is better.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17