How to Calculate BMI Percentile in Excel (4 Easy Methods)

This article shows you how to calculate the BMI percentile in Excel. A measure of body fat based on weight and height is called body mass index (BMI). Here, we will take you through 4 easy and convenient methods on how to calculate the BMI percentile in Excel.

What Is BMI Percentile?

The Body Mass Index, simply called BMI, is an easy and affordable approach to determine body fat using just height and weight. BMI is a screening technique that can be used to determine whether a person is underweight, overweight, or obese. The generic formula for calculating the BMI is as below.

BMI = weight (kg) / { height (m) }2

On the other hand, the BMI percentile represents relative value rather than an absolute figure. That means you can compare the individual’s BMI in relation to others using the BMI percentile. While calculating the BMI percentile, you need to compute the BMI first. Then you may determine the percentile in percentage.

How to Calculate BMI Percentile in Excel: 4 Easy Methods

Suppose, we have a Height and Weight List of some individuals including their Names, Heights in Feet and Inches, and Weights in lbs.

We want to calculate their BMI as well as their BMI percentile in Excel. So, without further ado, let’s jump into the different methods.

1. Using CONVERT and PERCENTRANK.INC Functions

Our first method comprises two different functions. To know more, follow the steps below.

Steps:

• At First, select cell F6. Write down the formula below and press ENTER.

Here, C6 and D6 represent the Height in Feet and Inches respectively.

Formula Breakdown:

We used the CONVERT function to convert the height in feet to meters. Again, we converted the height in inches into meters using the CONVERT function and added it to the previous portion of the formula with a ( + ) sign. Thus, we got the total height just in the meter unit.

• Secondly, use the Fill Handle tool and drag it down till cell F15. Therefore, we get the results of other cells.

• After that, we have to convert the weight from lbs to kg unit. Therefore, select cell G6 and write down the formula below. Then, press the ENTER key.

In this place, E6 serves as Weights in lbs.

Note: There is no default system in Excel to convert pounds into kilograms directly. So, first, we convert it into grams, then convert it into kilograms by dividing it by 1000.

• For calculating BMI, first, select cell H6 and type the formula below. Now, press ENTER.

• Later, select cell I6. Type in the formula below and press ENTER.

Here, H6:H15 means the range of these 10 people’s BMI, and just H6 represents the BMI of Robin.

The rank of a value in a data set is returned by the PERCENTRANK.INC function as a percentage of the entire data set. This function assesses a value’s position within a data set.

We can see that Mary has the lowest BMI at 17.71. On the other hand, Thomas has the highest BMI at 33.96. These 2 entities are represented as 0% and 100% respectively. All the other BMIs are in this range. For example, Robin has a BMI Percentile of 22. This means his BMI is greater than that of 22% of other people on this list.

Read More: How to Use the CONVERT Function to Calculate BMI in Excel

2. Applying CONVERT, POWER, and PERCENTRANK.INC Functions

In our second method, we’ll use the POWER function instead of using an index manually. Follow our steps carefully.

Steps:

• First, calculate the values up to column G just like we did in Method 1. This means finding out the Height (m) and Weight (kg) like in our previous method.
• Secondly, select cell H6. Type in the formula below and press ENTER.

Here, F6 and G6 represent the Height (m) and Weight (kg).

• Later, select cell I6 and write down the same formula as in Method 1 to get the BMI percentile.

3. Implementing Arithmetic Formula to Calculate BMI Percentile in Excel

In this method, we are using an arithmetic formula to calculate BMI from weight(lbs) and height(inches). The generic formula is as below.

BMI = weight (lbs) / { height (inches) }2 * 703

The main advantage of using this formula is that you don’t have to convert your height and weight to meters and kilograms. Follow our steps below to understand the method completely.

Steps:

• At first, select cell F6. Write down the formula below and press ENTER.

However, we converted the height of cell C6 from feet into inches by multiplying it by 12. Then add it to cell D6 to get the whole height in inches.

• Secondly, select cell G6 and put the formula as shown below. Then, press ENTER

Here, E6 and F6 serve as Weight(lbs) and Height(inch).

• Finally, calculate the BMI percentile just like we did in Method 1.

4. Employing VBA Code to Calculate BMI Percentile in Excel

Implementing the VBA code is an alternative way to solve this problem. Applying the VBA code is exciting all the time. Here, we’ve already calculated the Height (inch) like Method 3. Using the formula from our previous method, we will build a user-defined function through VBA coding to calculate BMI.

Notice the steps carefully.

Steps:

• First, right-click on the Sheet Name and select View Code.

• However, a window named Microsoft Visual Basic for Applications opens. After that, select Insert > Module.

• At this point, a coding module opens. Write down the code as shown below in that module.

Function BMI(w, h)
'Here, w means weight, h means height
BMI = (w / (h) ^ 2) * 703
End Function

• Later, click on the Save icon and save the workbook on your PC.

• After that, return to the VBA worksheet. Then, select cell G6 and start typing bmi with a preceding ( = ) sign. You can see that Excel is already showing the BMI function as a suggestion.

• After that, select the function by pressing TAB on the keyboard. Then, Give the argument as cell references E6 and F6.

• Lastly, find the BMI Percentile like Method 1 in cell H6.

Read More: How to Create a Body Mass Index Calculator in Excel Using VBA

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

Conclusion

Thank you for reading this article. We hope you find it helpful. Please let us know in the comment section if you have any queries or suggestions. Keep learning important features in Excel.

Related Articles

• How to Calculate BMI in Excel
• How to Calculate BMI Z Score in Excel