Why MMULT Covariance Results Differ from Excel Data Analysis in WPS Spreadsheets
Question details
The user wants to understand why a covariance matrix calculated with MMULT does not match Excel’s Data Analysis covariance results, especially the sample versus population divisor difference.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Comparing a manual MMULT covariance matrix with Excel’s Data Analysis covariance output for stock or other column data.
- Observed behavior
- MMULT covariance values differ from Excel Data Analysis covariance results because the manual formula often divides by n for population covariance, while Excel Data Analysis uses n-1 for sample covariance.
Before comparing results, confirm that both methods use the same data range, row orientation, and missing-value handling. Decide whether you need sample covariance using n-1 or population covariance using n.
Correct the MMULT Divisor to Match Excel Data Analysis Sample Covariance
Use this when you want your manual MMULT covariance matrix to match Excel Data Analysis, which calculates sample covariance with n-1.
Excel’s Data Analysis Covariance tool normally returns sample covariance, where each pairwise covariance is divided by n-1. A manual MMULT formula that divides by the number of observations n produces population covariance, causing the mismatch.
In WPS Spreadsheets, create a helper area the same size as your data. For each column, enter =A2-AVERAGE(A$2:A$61) and copy across all columns and rows to center the observations.
Click an empty cell large enough to hold the p-by-p result, then type =MMULT(TRANSPOSE(centered_data),centered_data)/(COUNT(data)-1).
Press Enter if WPS Spreadsheets spills the array. If not, select the full output range, type the formula, and press Ctrl+Shift+Enter to enter it as an array formula.
Use COVARIANCE.S for Individual Covariance Pairs
Use this when you only need specific pairwise covariance values and want a simpler formula that matches Excel’s sample covariance result.
Calculate Covariance Matrices Correctly with WPS Spreadsheets
WPS Spreadsheets gives you MMULT, TRANSPOSE, COVARIANCE.S, and COVARIANCE.P to reproduce or check Excel Data Analysis covariance results. Use the n-1 divisor for sample covariance and n for population covariance, all in a free spreadsheet familiar to Excel users.
- 1. Open WPS Spreadsheets and load your data: Launch WPS Office, click Spreadsheets, and open the workbook containing your stock or column data.
- 2. Create centered observations: In a helper range, subtract each column mean from each value, such as =A2-AVERAGE(A$2:A$61), and fill across all variables.
- 3. Enter the MMULT formula: Click a blank cell and type =MMULT(TRANSPOSE(centered_range),centered_range)/(COUNT(data)-1) for sample covariance.
- 4. Apply array entry if needed: Press Enter if the formula spills, or select the output range, type the formula, and press Ctrl+Shift+Enter.
- 5. Compare the matrix with Excel Data Analysis: Check that the same rows and divisor are used. The sample covariance matrix should match the corresponding Excel Data Analysis output.
Frequently Asked Questions
Why does my MMULT covariance divide by n but Excel Data Analysis uses n-1?
Excel Data Analysis’s Covariance tool calculates sample covariance by default, so it divides by n-1. A manual MMULT formula that divides by the number of observations n calculates population covariance.
How do I center data before using MMULT in WPS Spreadsheets?
Create a helper range and subtract each column’s mean, for example =A2-AVERAGE(A$2:A$61). Fill the formula across all rows and columns before applying MMULT.
Which WPS function should I use to match Excel Data Analysis covariance?
Use COVARIANCE.S for sample covariance. Enter =COVARIANCE.S(range1,range2) or find it under Formulas > Insert Function.
Does MMULT need Ctrl+Shift+Enter in WPS Spreadsheets?
If WPS Spreadsheets does not spill the result automatically, select the full output range, type the MMULT formula, and press Ctrl+Shift+Enter to enter it as an array formula.
What should I check if my MMULT covariance still differs from Excel?
Check data orientation, same rows, missing-value treatment, mean calculation, and whether you are comparing sample covariance with n-1 or population covariance with n.
