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.