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.

**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)`

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

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

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

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