How to Calculate BMI Z Score in Excel (with Quick Steps)

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.

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²  

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:

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

• 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,

• 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

• 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,

• 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

• 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