In statistics, the **Z Score** denotes a value’s position or rank in the dataset relative to the mean. Calculating that dataset’s **BMI **and **Z Score **of that Value will help us determine the relative position of that **BMI**. If you are curious to know how you can** calculate the BMI value **and **Calculate the** **Z Score** of that value, this article could come in handy. In this article, we determine, how you can calculate the **BMI Value** from a patient’s height and weight and then calculate the **Z Score** of those **BMI values** in **Excel** with detailed explanations.

**Table of Contents**hide

## Download Practice Workbook

Download this practice workbook below.

## What Is BMI?

**BMI (Body Mass Index) **is a standard index first proposed by **Adolphe Quetelet **in the **1830s**. The main purpose of gives an insight into a person’s physical fitness status and categorize them. The formula involves a person’s **Height **and **Weight**. The general expression of **BMI **is:

**BMI**=**Weight**/**Height ^{2 }_{ }**

Although It is a widely known and used parameter, it has some demerits. As in some cases, the **BMI** could be misleading. Athletes usually have higher weight compared to others because of their high muscle weight. But on the **BMI scale**, they would be considered obese or overweight. For the same reason, underweight people could be falsely categorized as having normal **BMI**.

## What Is Z Score?

The basic formula for calculation of the **Z Score** is given below:

Here,

**x **= The raw data.

**µ **= Mean/Average of the dataset.

**σ **= The standard deviation of the given dataset.

**Z **= Final score of each data.

We can easily interpret the **Z Score **value of each data. **Z Score **tells us the distance of each data from the **Mean **in the **Standard Deviation **unit. In other words, how many **Standard Deviations** distance each value from the mean value. If it’s 0, then the value is in the mean value.

If the **Z Score **positive 1. Then the value is 1 **Standard Deviation **above the **Mean value**. If it’s -1, then the value is a **Standard Deviation **below the **Mean value**. We can also put the values also in the** normal distribution curve.**

## Step-by-Step Procedure to Calculate BMI Z Score in Excel

For the demonstration purpose, we are going to use the below dataset, where the height and the weight value of patients are listed in the **Height(m) **and in the **Weight(kg) **columns.

Using the dataset we are going to calculate the **BMI value** and then the **Z Score** of that **BMI value **in Excel.

### Step 1: Prepare Dataset

Before we jump into calculating the **BMI** and its **Z values**, we need to prepare the dataset otherwise it could yield corrupted and misleading results. Follow the below points in order to prepare the dataset.

- At first, we need to prepare the dataset for the calculation of the
**BMI score**based on the**Height**and**Weight**and then rank them on the basis of the**Z Score**. But before doing this, we need to organize the**raw data**in order to calculate the**Z Score**of the**BMI values.** - We need to convert the unit of the data if it isn’t in the right unit in the first place.
- In order to calculate the
**BMI**, we need to have the height and the weight values. And the**Height**and**Weight**values must be in the**Metric****Units.**This means, the**Weight**must be in**KG**and the**Height**must be in the**Meter.**If the height and the weight is in the other unit, then they must be converted back to the**Metric Units**.

### Step 2: Calculate BMI

After we convert the units back to the** Metric unit**, it’s time to calculate the **BMI value **of the given patient’s **Height** and **Weight **values.

**Steps**

- In the beginning, select the cell
**E5**and the following formula

`=D5/(C5*C5)`

- Upon entering the formula, the
**BMI**of the person in cell**B5**is calculated and placed in cell**E5.**

- Then we drag the
**Fill Handle**in the corner of cell**E5**to cell**E14.** - Doing this will fill the range of cells
**E5:E14**with the range of cells**B5:B14**people’s**BMI**value

**Read More: ****How to Calculate BMI Percentile in Excel (4 Easy Methods)**

### Step 3: Calculate Standard Deviation and Mean of Dataset

Now we will calculate the **Standard deviation** using **the STDEV.P function** and the** Average** using **the AVERAGE function** of the **BMI values** calculated in the previous step.

**Steps**

- In order to do this, we first need to select the cell
**J5**and enter the following formula,

`=STDEV.P(E5:E14)`

- It will calculate the
**Standard Deviation**of the range of cells**E5:E14,**which is actually the**BMI**weight values that we calculated earlier.

- Now we will select the cell
**J6**and the below formula

`=AVERAGE(E5:E14)`

- Entering this formula will calculate the mean of the data in the range of cells
**E5:E14,**which is basically the**BMI weight**values of the patients mentioned in the range of cells**B5:B14**.

**Read More: How to Calculate Probability from Z-Score in Excel (with Quick Steps)**

### Step 4: Calculate Z Score of Each Data

After calculating the **BMI values **with the **Mean **and **Standard Deviation **of it, we finally calculate the **Z Score** of each of them.

**Steps**

- Now, to calculate the
**Z Score**of the calculated**BMI weight values**, we have all the necessary parameters. - Next, we select the cell
**F5**and enter the following formula,

`=(E5-$J$6)/$J$5`

- Entering this formula will calculate the
**Z-Score**of the**BMI value**in cell**E5**, among all the other**BMI values.**

** **

- Then drag the
**Fill Handle**to cell**F14**. Doing this will calculate the**Z Score**of each entry in the range of cells**E5:E14.**

**Alternative Way to Calculate Z Score**

You can use** the STANDARDIZE function** to calculate the **Z Score **of each **BMI** value.

**Steps**

- Select the cell
**F5**and enter the following formula

`=STANDARDIZE(E5,$J$6,$J$5)`

- Then drag the
**Fill Handle**to cell**F14,**this will fill the range of cells**F5:F14**with a**Z Score**of each**BMI value**mentioned in the range of cells**B5:B14.**

This is the alternative way in which we can calculate the **Z Score** of the **BMI values**

** **

**Read More: How to Calculate Critical Z Score in Excel (3 Suitable Examples)**

## Conclusion

To sum it up, the question “how to calculate **BMI Z Scores** in Excel ” is answered here by using the **Standard Deviation, Average,** and** Standardize **functions. Before calculating the score, we need to calculate the **BMI value** first.

For this problem, a macro-enable workbook is available for download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the **Exceldemy** community will be highly appreciable.

## Related Articles

**Create a Body Mass Index (BMI) Calculator in Excel Using VBA****How to Calculate Weight Gain or Loss in Excel (5 Easy Methods)****Weight Loss Spreadsheet in Stones and Pounds (2 Useful Examples)****How to Calculate Altman Z Score in Excel (3 Suitable Examples)****How to Calculate P Value from Z Score in Excel (with Quick Steps)****How to Use the CONVERT Function in Excel and creating a BMI Calculator template**