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.

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²  

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:

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

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

• 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

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

• 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

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