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.


Download Practice Workbook

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


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.


4 Methods to Calculate BMI Percentile in Excel

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

how to calculate bmi percentile in excel dataset

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.
=CONVERT(C6,"ft","m")+CONVERT(D6,"in","m")

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.

how to calculate bmi percentile in excel using CONVERT function

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

how to calculate bmi percentile in excel using CONVERT function

  • 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 ENTER key.
=CONVERT(E6,"lbm","g")/1000

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.

how to calculate bmi percentile in excel using CONVERT function

  • For calculating BMI, first, select cell H6 and type the formula below. Now, press ENTER.
=G6/F6^2

how to calculate bmi percentile in excel using CONVERT function

  • Later, select cell I6. Type in the formula below and press ENTER.
=PERCENTRANK.INC($H$6:$H$15,H6)

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.

how to calculate bmi percentile in excel using CONVERT function

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 in this list.

Read More: How to Use the CONVERT Function in Excel and creating a BMI Calculator template


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:

  • At 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.
=G6/POWER(F6,2)

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

how to calculate bmi percentile in excel using POWER function

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

how to calculate bmi percentile in excel using POWER function

Read More: How to Calculate BMI in Excel (2 Handy Ways)


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.
=C6*12+D6

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

how to calculate bmi percentile in excel using POWER function

  • Secondly, select cell G6 and put the formula as shown below. Then, press ENTER
=E6/F6^2*703

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

how to calculate bmi percentile in excel using POWER function

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

how to calculate bmi percentile in excel using POWER function

Read More: How to Use Formula for Tracking Weight Loss in Excel (2 Examples)


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.

how to calculate bmi percentile in excel applying VBA code

Notice the steps carefully.

Steps:

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

how to calculate bmi percentile in excel applying VBA code

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

Applying VBA code

  • 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

Applying VBA code

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

Applying VBA code

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

Applying VBA code

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

Applying VBA code

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

Applying VBA code

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


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. Please visit our website, Exceldemy, to explore more.


Related Articles

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo