COVARIANCE.S in Excel: Sample Covariance and the n Minus One Denominator

• Introduced: the Microsoft function catalogue marks COVARIANCE.S as an Excel 2010 addition; an exact first build or preview date is not established here.
• Availability: official help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016, Excel Web App.
• Checked: October 14, 2026; official COVARIANCE.S reference. Documentation and independently modeled examples; no native Excel execution or broader platform certification.

=COVARIANCE.S(array1, array2)

Both inputs are required. Rows must describe corresponding measurements. A sample covariance calculation cannot repair mismatched records or justify independently sorting the columns.

Microsoft’s short argument descriptions say integers, but the surrounding remarks allow numbers. The examples here use ordinary integer-valued pairs without interpreting that wording as a verified integer-only restriction.

Enter X 1,2,3,4 in A2:A5 and Y 2,1,4,3 in B2:B5.

=COVARIANCE.S(A2:A5,B2:B5)

Expected result: 1. Both means are 2.5. The four centered products are 0.75 each, giving numerator 3. Sample covariance divides by 4-1=3.

For the same data, COVARIANCE.P gives 0.75, dividing by four. The sample value is the population-convention value multiplied by n/(n-1)=4/3 here. These are two denominator conventions applied to one set of records.

Enter (0,0), (1,2), (2,4) in D2:E4.

=COVARIANCE.S(D2:D4,E2:E4)

Expected result: 2. The means are 1 and 2, the centered-product sum is 4, and the sample denominator is two. The zero pair participates normally.

The population-convention answer would be 4/3. A report that rounds both results too heavily can hide a real methodological difference. Keep the chosen function visible alongside the sample count.

In G2:H9 repeat the original four pairs twice, in the same paired order. The means remain 2.5 and 2.5, while the centered-product sum doubles to 6.

=COVARIANCE.S(G2:G9,H2:H9)

Expected result: 6/7, approximately 0.857142857. It differs from the original sample covariance of 1 because the denominator changes from 3 to 7, not from 3 to 6.

The corresponding population covariance stays 0.75. This difference is a useful audit signal when a join duplicates records. Repeating rows does not create additional independent observations merely because the formula’s count increases.

For the original four pairs, replacing Y with -2,-1,-4,-3 gives sample covariance -1. Replacing Y with 20,10,40,30 gives 10. Adding the same constant to every Y value leaves the covariance at 1.

A constant X vector 5,5,5,5 paired with the original Y has modeled sample covariance 0, because every X deviation is zero and the denominator remains three. That does not imply a defined correlation coefficient: CORREL requires nonzero variation in both vectors.

These algebraic checks are often more useful than copying a single unexplained output. They expose sign reversals, unit conversions and accidental constant columns.

Microsoft specifies #N/A for different numbers of data points and #DIV/0! for empty inputs or only one data point in each input. A one-pair covariance cannot be repaired by replacing its division error with zero.

Text, logical values and empty reference cells are documented as ignored; zeros are included. This does not establish every independently missing-cell or error-valued pairing case. These examples intentionally use complete numeric pairs, and no native mixed-input behavior is claimed.

For a sample interpretation, also examine how observations were selected, whether repeated rows are genuine repeated measurements and whether the same record key aligns both columns. The formula’s denominator is a mathematical convention, not a guarantee of sound sampling.

• Microsoft COVARIANCE.S reference.
• Editorial testing standards.

Expected results are independent statistical/arithmetic models or explicitly documented rules, not captured native output. Examples use English function names and comma separators. Datasets and reporting policies are fictional.