Maruf Islam
Excel users who deal with Statistical data often calculate various properties regarding Statistics. So, users sometimes need to calculate Mahalanobis Distance in Excel.
Let’s say we have monthly return percentages of Stocks and Bonds. And we want to calculate the Mahalanobis Distance (DM).
In this article, we demonstrate the step-by-step process to calculate the Mahalanobis Distance (DM) in Excel.
Download Excel Workbook
What Is Mahalanobis Distance?
Mahalanobis Distance (DM) refers to the distance between a point and a distribution. It doesn’t mean the typical distance between two specific points. It’s the multivariate equivalent of the Euclidean distance. Usually, users use Mahalanobis Distance (DM) mostly in Statistics applications. The fundamental formula for calculating Mahalanobis Distance (DM) is
In the formula,
X = the vector observation
µ = the vector Mean of the observations
S-1 = inverse Covariance matrix
Steps to Calculate Mahalanobis Distance in Excel
There are several components of the Mahalanobis Distance (DM) formula that need to be computed first before calculating the Mahalanobis Distance (DM). The components are
(i) Calculating Mean (µ)
(ii) Calculating Mean Difference (x-µ)
(iii) Finding Covariance Matrix (S)
(iv) Transposing the Mean Difference ((x-µ)T)
(v) Finding Inverse Covariance Matrix (1/S)
(vi) Multiplying Mean Difference (x-µ) and Inverse Covariance Matrix (1/S)
(vii) Multiplying all Mahalanobis Distance Components ((1/S)*(x-µ)*((x-µ)T)))
(viii) Mahalanobis Distance (DM) calculation
Follow the below section to finally find the Mahalanobis Distance (DM) from the given dataset.
🔼 Calculating Mean (µ) from Values
⏩ In the dataset, there are two columns named Stocks and Bonds. Before calculating the Mahalanobis Distance (DM), users have to find the Mean. Use the following formula in cell C15 to find the Mean.
=SUM(C5:C14)/COUNT(C5:C14)
⏩ Apply the previous formula for Bonds (i.e., Column D) to find the Mean.
You can use the AVERAGE function to find the Mean.
🔼 Calculating Mean Difference (x-µ) of Values
⏩ After calculating the Mean, users have to find the Mean Difference (x-µ) of the values. Execute the following formula in cell F5 to find the Mean Difference (x-µ) for individual entries.
=C5-$C$15
⏩ Use the Fill Handle to apply the formula in other cells.
⏩ Repeat the previous steps with the same formula to find the Mean difference for the Bonds (i.e., Column G) column.
🔼 Finding Covariance Matrix (S) Using Data Analysis
⏩ The fundamental part of finding Mahalanobis Distance is finding the Covariance matrix. Go to the Data tab > Click on Data Analysis (in the Analysis section).
⏩ Excel brings the Data Analysis window. Choose Covariance then click on OK.
⏩ The Covariance dialog box opens up. Provide the necessary ranges such as F4:G14 (Mean Difference Range) as Input Range, and I4 as Output Range. Ensure the ticking of the option- Labels in first row. Afterward, click on OK.
⏩ Clicking OK inserts the Covariance Matrix(S) as depicted in the below image.
⏩ Excel in-built doesn’t insert all the entries. Therefore, Copy (CTRL+C) and Paste (CTRL+V) the J6 value in cell K5.
⏩ Use the MINVERSE function to get the Inverse Covariance Matrix. Press CTRL+SHIFT+ENTER to apply the formula below formula. Make sure the Covarane Matrix is a Square Matrix.
=MINVERSE(J5:K6)
Read More: How to Calculate Levenshtein Distance in Excel (4 Easy Methods)
🔼 Transposing Mean Difference Matrix ((x-µ)T)
⏩ Now, users need the transposed Mean Difference Matrix. Use the Transpose function or Copy (CTRL+C) the Mean Difference range (F5:G14).
⏩ After that, highlight any range and right-click on it. Click on the Transpose option to insert the transposed Mean Difference Matrix (x-µ)T.
🔺 The final Transpose Mean Difference Matrix looks similar to the below image.
Similar Readings
- How to Calculate Distance Between Two Addresses in Excel (3 Ways)
- Calculate Miles between Two Addresses in Excel (2 Methods)
- How to Calculate Distance Between Two Coordinates in Excel (2 Methods)
- Calculate Manhattan Distance in Excel (2 Suitable Ways)
🔼 Multiplication of Inverse Covariance Matrix (1/S) and Mean Difference (x-µ)
⏩ As the users have all the necessary components, users can multiply the Inverse Covariance Matrix (1/S) and Mean Difference (x-µ). Place the following formula in cell I5.
=MMULT(F5:G14,L5:M6)The MMULT function array multiplies the given two arrays.
⏩ Since the formula is an array formula, use CTRL+SHIFT+ENTER to execute it.
🔼 Multiplication of All Mahalanobis Distance Components ((1/S)*(x-µ)*((x-µ)T))
⏩ Multiply the portion Inverse Covariance Matrix (1/S), Mean Difference (x-µ), and Transpose Mean Difference (x-µ)T to get the final component of the Mahalanobis formula. Use the following formula in cell N8.
=MMULT(I5:J14,M4:V5)
⏩ Hit CTRL+SHIFT+ENTER to apply the array formula in all cells. In the end, users get an out as shown in the image below.
🔼 Calculation of Mahalanobis Distance
⏩ Square rooting all the diagonal values of the previous array outcomes (colored background) results in the Mahalanobis Distance. Type the below formula in cell W3 to find the distance.
=SQRT(L3)
⏩ Similarly, use the diagonal values such as M4, N5… to find the Mahalanobis Distances. Mahalanobis Distance results in #NUM! error if the final multiplication value results in minus.
Conclusion
In this article, we demonstrate the step-by-step process to calculate the Mahalanobis Distance in Excel. We discuss each component of its formula and demonstrate its calculations. Hope this article helps you to clarify the process and be able to calculate the Mahalanobis Distance (DM). Comment, if you have further inquiries or have anything to add.
