How to Calculate Mahalanobis Distance in Excel (Step by Step)

Get FREE Advanced Excel Exercises with Solutions!

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).

Dataset-Calculate Mahalanobis Distance in Excel

In this article, we demonstrate the step-by-step process to calculate the Mahalanobis Distance (DM) in Excel.


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

Mahalanobis Distance Formula

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)

Calculating Mean-Calculate Mahalanobis Distance in Excel

⏩ Apply the previous formula for Bonds (i.e., Column D) to find the Mean.

Calculating 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

Mean Difference-Calculate Mahalanobis Distance in Excel

⏩ Use the Fill Handle to apply the formula in other cells.

Fill Handle

⏩ Repeat the previous steps with the same formula to find the Mean difference for the Bonds (i.e., Column G) column.

Mean Difference


🔼 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).

Covariance Matrix-Calculate Mahalanobis Distance in Excel

⏩ Excel brings the Data Analysis window. Choose Covariance then click on OK.

Covariance Add-in

⏩ 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.

Covariance window

⏩ Clicking OK inserts the Covariance Matrix(S) as depicted in the below image.

Covariance Matrix

⏩ Excel in-built doesn’t insert all the entries. Therefore, Copy (CTRL+C) and Paste (CTRL+V) the J6 value in cell K5.

Covariance Matrix

⏩ 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)

Inverse Covariance Matrix


🔼 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).

Transpose Mean Difference-Calculate Mahalanobis Distance in Excel

⏩ After that, highlight any range and right-click on it. Click on the Transpose option to insert the transposed Mean Difference Matrix (x-µ)T.

Transpose

🔺 The final Transpose Mean Difference Matrix looks similar to the below image.

Transposed Mean Difference


🔼 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.

Multiplication

⏩ Since the formula is an array formula, use CTRL+SHIFT+ENTER to execute it.

Multiplication result


🔼 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)

Final Multiplication-Calculate Mahalanobis Distance in Excel

⏩ 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.

Result


🔼 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)

Mahalanobis Distance-Calculate Mahalanobis Distance in Excel

⏩  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.

Mahalanobis Distance


Download Excel Workbook


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.


<< Go Back to Distance | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo