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** (**D _{M}**).

In this article, we demonstrate the step-by-step process to calculate the **Mahalanobis Distance** (**D _{M}**) in Excel.

**Table of Contents**hide

**Download Excel Workbook**

**What Is Mahalanobis Distance?**

**Mahalanobis Distance** (**D _{M}**) 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**(

**D**) mostly in Statistics applications. The fundamental formula for calculating

_{M}**Mahalanobis Distance**(

**D**) is

_{M}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** (**D _{M}**) formula that need to be computed first before calculating the

**Mahalanobis Distance**(

**D**). The components are

_{M}(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** (**D _{M}**) calculation

Follow the below section to finally find the **Mahalanobis Distance** (**D _{M}**) from the given dataset.

**đź”Ľ Calculating Mean (****Âµ) from Values**

âŹ© In the dataset, there are two columns named **Stocks** and **Bonds**. Before calculating the **Mahalanobis Distance** (**D _{M}**), 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})

^{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}))

^{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** (**D _{M}**). Comment, if you have further inquiries or have anything to add.