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**Expand

## What Is BMI?

**BMI (Body Mass Index) **is a standard index first proposed by **Adolphe Quetelet **in the **1830s**. The main purpose of give 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. In some cases, the **BMI** could be misleading. Athletes usually have higher weights 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 a normal **BMI**.

## What Is Z Score?

The basic formula for the 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 **is 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 in the normal distribution curve.

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

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

### 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 value 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**.

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

After calculating the **BMI **values with the **Mean **and **Standard Deviation**, 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

** **

**Download Practice Workbook**

Download this practice workbook below.

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

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